Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I vlookup multiple rows with same reference? | Excel Worksheet Functions | |||
Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
VLookup using 2 worksheets, whole column reference | Excel Worksheet Functions | |||
vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions |