Vlookup with Multiple like values in the reference column
 

I recieve a large spreadsheet daily from headquarters that shows locations of
clients per their applications. I need to determine a count of how many
clients fit into the pre-determined categories. Unfortunately, the data group
at headquarters formats the information by Zip code into multiple rows with
the same ZIP in the column. I need to find a way to either combine the
multiple columns into one row, or have Vlookup ignore blank cells and find a
populated cell.

I'm not explaining that well, so here's an example

Zip MH SingFam Condo TT
61284 11 2
61284 56
61284 12
61201 12
61201 3
42809 4
34576 23 2
34576 2
54576 3

It's driving me mad. If they would put them all into one row, I could
Vlookup.. but since it returns the first value in the corresponding cell, I
can't get the one with the information. Please help


--
Chris Kaiser
Apprentice Excel Ninja

Vlookup with Multiple like values in the reference column
 

I'd probably try Data Subtotals, and create a SUM of MH, SingFam, Condo, TT
for each change in zip (I'm assuming the data is sorted by zip, as your
sample suggests). Then, instead of looking up 61284, lookup '61284 Total'.
You can put that in a formula with the & operator, eg =vlookup(a1 & "
Total",.....).
HTH. --Bruce

"ckemtp" wrote:

I recieve a large spreadsheet daily from headquarters that shows locations of
clients per their applications. I need to determine a count of how many
clients fit into the pre-determined categories. Unfortunately, the data group
at headquarters formats the information by Zip code into multiple rows with
the same ZIP in the column. I need to find a way to either combine the
multiple columns into one row, or have Vlookup ignore blank cells and find a
populated cell.

I'm not explaining that well, so here's an example

Zip MH SingFam Condo TT
61284 11 2
61284 56
61284 12
61201 12
61201 3
42809 4
34576 23 2
34576 2
54576 3

It's driving me mad. If they would put them all into one row, I could
Vlookup.. but since it returns the first value in the corresponding cell, I
can't get the one with the information. Please help


--
Chris Kaiser
Apprentice Excel Ninja

Vlookup with Multiple like values in the reference column
 

Thanks a ton. It does add a few steps, but it really simplified my day. Thank
you a ton.
--
Chris Kaiser
Apprentice Excel Ninja


"bpeltzer" wrote:

I'd probably try Data Subtotals, and create a SUM of MH, SingFam, Condo, TT
for each change in zip (I'm assuming the data is sorted by zip, as your
sample suggests). Then, instead of looking up 61284, lookup '61284 Total'.
You can put that in a formula with the & operator, eg =vlookup(a1 & "
Total",.....).
HTH. --Bruce

"ckemtp" wrote:

I recieve a large spreadsheet daily from headquarters that shows locations of
clients per their applications. I need to determine a count of how many
clients fit into the pre-determined categories. Unfortunately, the data group
at headquarters formats the information by Zip code into multiple rows with
the same ZIP in the column. I need to find a way to either combine the
multiple columns into one row, or have Vlookup ignore blank cells and find a
populated cell.

I'm not explaining that well, so here's an example

Zip MH SingFam Condo TT
61284 11 2
61284 56
61284 12
61201 12
61201 3
42809 4
34576 23 2
34576 2
54576 3

It's driving me mad. If they would put them all into one row, I could
Vlookup.. but since it returns the first value in the corresponding cell, I
can't get the one with the information. Please help


--
Chris Kaiser
Apprentice Excel Ninja

Vlookup with Multiple like values in the reference column
 

You could also use the sumif function... instead of =vlookup(value,A:B,2,0)
to return the first entry from column B where column A matches the given
value, it would be =sumif(a:a,value,b:b). In words, find the value in column
A and add the corresponding entries from column B.

"ckemtp" wrote:

Thanks a ton. It does add a few steps, but it really simplified my day. Thank
you a ton.
--
Chris Kaiser
Apprentice Excel Ninja


"bpeltzer" wrote:

I'd probably try Data Subtotals, and create a SUM of MH, SingFam, Condo, TT
for each change in zip (I'm assuming the data is sorted by zip, as your
sample suggests). Then, instead of looking up 61284, lookup '61284 Total'.
You can put that in a formula with the & operator, eg =vlookup(a1 & "
Total",.....).
HTH. --Bruce

"ckemtp" wrote:

I recieve a large spreadsheet daily from headquarters that shows locations of
clients per their applications. I need to determine a count of how many
clients fit into the pre-determined categories. Unfortunately, the data group
at headquarters formats the information by Zip code into multiple rows with
the same ZIP in the column. I need to find a way to either combine the
multiple columns into one row, or have Vlookup ignore blank cells and find a
populated cell.

I'm not explaining that well, so here's an example

Zip MH SingFam Condo TT
61284 11 2
61284 56
61284 12
61201 12
61201 3
42809 4
34576 23 2
34576 2
54576 3

It's driving me mad. If they would put them all into one row, I could
Vlookup.. but since it returns the first value in the corresponding cell, I
can't get the one with the information. Please help


--
Chris Kaiser
Apprentice Excel Ninja