Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 171
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 171
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I vlookup multiple rows with same reference? Kathie C Excel Worksheet Functions 2 March 30th 08 07:09 PM
Adding multiple values in one column based on multiple values of the same value (text) in another column [email protected] Excel Discussion (Misc queries) 1 May 16th 07 06:02 PM
Adding numerical values based on multiple values in another column Kazmaniac Excel Worksheet Functions 6 April 4th 07 08:53 PM
VLookup using 2 worksheets, whole column reference ROSIE Excel Worksheet Functions 1 February 22nd 06 01:50 AM
vlookup, multiple values, sum values into one cell?? Phillips L Excel Worksheet Functions 4 November 9th 05 01:31 AM


All times are GMT +1. The time now is 06:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"