• Welcome to OGBoards 10.0, keep in mind that we will be making LOTS of changes to smooth out the experience here and make it as close as possible functionally to the old software, but feel free to drop suggestions or requests in the Tech Support subforum!

The Excel Help Thread

IamThunderbolt

IamThunderbolt

Well-known member
Joined
Mar 22, 2011
Messages
7,510
Reaction score
4,072
Location
Greensboro, bitches!
Excel Wizard Help Needed

hey Guys

I asked this on the old board a while ago and can't find the thread (yay no search function), but how do you combine non-numerical data from different columns into a single column. For example:

Column A - Address
Column B - City
Column C - State
Column D - Zip Code

Desired Column E - Address City, State Zipcode
 
Last edited:
Concatenate

=CONCATENATE(A1,B1,",",C1,D1)
 
or you can just use the "&" sign. it does the same thing.

so you'd have =A1&B1&C1&D1

you'd also (in either Ph's solution or mine) probably need to put in quotation marks between each one with a space so that it's not all crammed together and you actually have a space separating each part of it. so you'd actually end up with:

=A1&" "&B1&" "&C1&" "&D1
 
DeacHoops said:
or you can just use the "&" sign. it does the same thing.

so you'd have =A1&B1&C1&D1

you'd also (in either Ph's solution or mine) probably need to put in quotation marks between each one with a space so that it's not all crammed together and you actually have a space separating each part of it. so you'd actually end up with:

=A1&" "&B1&" "&C1&" "&D1
Click to expand...

This...pos rep
 
thanks guys, I appreciate it - especially ph for being on the ball at midnight last night, allowing me to finish and get to sleep within 30 minutes of that post.
 
DeacHoops said:
or you can just use the "&" sign. it does the same thing.

so you'd have =A1&B1&C1&D1

you'd also (in either Ph's solution or mine) probably need to put in quotation marks between each one with a space so that it's not all crammed together and you actually have a space separating each part of it. so you'd actually end up with:

=A1&" "&B1&" "&C1&" "&D1
Click to expand...

Almost this. If you want a comma between the address, city and state, you'd want to insert ", " (comma and space) in between each cell. If you don't want a comma, DeacHoops is 100% correct.

if A1="1834 Wake Forest Drive"
if B1="Winston-Salem"
if C1="North Carolina"
if D1="27109"

then the formula
=A1&", "&B1&", "&C1&" "D1

would yield
1834 Wake Forest Drive, Winston-Salem, North Carolina 27109
 
Last edited:
IamThunderbolt said:
thanks guys, I appreciate it - especially ph for being on the ball at midnight last night, allowing me to finish and get to sleep within 30 minutes of that post.
Click to expand...

This is why I almost always post tech questions on the boards when I have them, in addition to googling a solution.
 
smallbigtall said:
Almost this. If you want a comma between the address, city and state, you'd want to insert ", " (comma and space) in between each cell. If you don't want a comma, DeacHoops is 100% correct.

if A1="1834 Wake Forest Drive"
if B1="Winston-Salem"
if C1="North Carolina"
if D1="27109"

then the formula
=A1&", "&B1&", "&C1&" "D1

would yield
1834 Wake Forest Drive, Winston-Salem, North Carolina 27109
Click to expand...

truth. forgot about the commas.
 
I'm fairly inexperienced with excel and need some help. Thanks in advance to any excel wiz out there who can help me.

I am trying to assign values to text. So if I type Yes into a chart, that cell would have a value of 2. If I type No into my chart that cell would have a value of 1. I also want to assign number values to poor neutral and good. Then I want a sum of the values in a particular column at the bottom. So far this is the formula I have:

=SUM(E3:E30),IF(E3:E30="Yes",2)(E3:E30="No",1)(E3:E30="Poor",1)(E3:E30="Neutral",2)(E3:E30="Good",3))

I tried Googling around and couldn't quite get it figured out.
 
GreenDeac said:
I'm fairly inexperienced with excel and need some help. Thanks in advance to any excel wiz out there who can help me.

I am trying to assign values to text. So if I type Yes into a chart, that cell would have a value of 2. If I type No into my chart that cell would have a value of 1. I also want to assign number values to poor neutral and good. Then I want a sum of the values in a particular column at the bottom. So far this is the formula I have:

=SUM(E3:E30),IF(E3:E30="Yes",2)(E3:E30="No",1)(E3:E30="Poor",1)(E3:E30="Neutral",2)(E3:E30="Good",3))

I tried Googling around and couldn't quite get it figured out.
Click to expand...

Why not have a separate IF column next to the Yes/No/Neutral/Poor and then sum that one?
 
Thanks guys!

Ok, I tried to do a separate column and now I am getting a #value! error with this formula

=IF(E3:E30="Yes",2)(E3:E30="No",1)(E3:E30="Poor",1)(E3:E30="Neutral",2)(E3:E30="Good",3)
 
GreenDeac said:
Thanks guys!

Ok, I tried to do a separate column and now I am getting a #value! error with this formula

=IF(E3:E30="Yes",2)(E3:E30="No",1)(E3:E30="Poor",1)(E3:E30="Neutral",2)(E3:E30="Good",3)
Click to expand...

You need to nest the IF statements

=IF(E3="Yes",2,IF(E3="No",1,IF(E3="Poor",1)))

And then copy your formula for the rest of the column.
 
i'm technically inclined, but what in the fuck are you trying to say?
 
Excel Help

Figured I would come to the Pit because there are a lot of smart people/computer nerds here.

I have two spreadsheets in my Excel workbook. The first spreadsheet is filled with data. The second spreadsheet uses formulas that pull data from the first spreadsheet.

I want both spreadsheets to be sortable. The problem I have is that when I sort the first spreadsheet, it screws up the formulas on the second spreadsheet.

Basically, say that the first cell on the second spreadsheet is =sum('Spreadsheet1'!A1:A3). When I sort the first spreadsheet, the second spreadsheet is still pulling its data from A1:A3 rather than changing the references so that it's still pulling the same numbers, even though they are now located somewhere else.

Is there a way to fix this?
 
utilize sumif functions rather than direct cell references broski
 
You must log in or register to reply here.
Back
Top