Look up functions and a one to many relationship

[Guest]

Creating a report - What function can I use to look up a value from one
column in sheet 1 and find all rows in sheet 2 that contain the matching
value?

Sheet one contains Customer Identification numbers. (Oh, and the same
Customer can have multi ID numbers - unique to address)
Sheet two contains product information for the customers.

EX:
Sheet 1:
123 Invotech
146 Bank of Trusted Presidents

Sheet 2:
123 spreadsheet
123 word processing
123 accounting
123 payroll
146 accounting
146 pos retail
146 health services

Report should show the customer name and columns across with the product
names.

 

[Pete_UK]

In Sheet 2 put this formula in C1:

=A1&"_"&COUNTIF(A$1:A1,A1)

and copy down. In Sheet 1 you need a header row, so insert a row at
the top if you don't already have one. You can put "ID" and "Name" in
A1:B1, then in C1 across enter the numbers 1, 2, 3, 4 ... etc.

Then in C2 you can enter this formula:

=IF(ISNA(MATCH($A2&"_"&C$1,Sheet2!$C$1:$C$100,0)),"",INDEX(Sheet2!$B
$1:$B$100,MATCH($A2&"_"&C$1,Sheet2!$C$1:$C$100,0)))

I have assumed you have 100 rows of data in Sheet2 - adjust to suit,
then copy the formula across for as many columns as you wish. Then
copy these formulae down into as many rows as you need.

Hope this helps.

Pete

 

[Guest]

In actuality, sheet one will have upwards of 600 records with unique
customer info and customer id numbers, sheet two currently has 80,000
records/rows - not all of which have a match (single or otherwise) in sheet
one.

Why? Will this make a difference????

 

[Pete_UK]

Wherever I've got $100 in my formula (3 times) you will need to make
this $80000 if that's how many records you have. Make the change while
it is still in C2, and then copy it across and down. You may need to
adjust the column widths to see all your returned data.

Why? Will this make a difference????

If you don't make the change then the formula will only look at the
first 100 rows of data in Sheet2.

Hope this helps.

Pete

 

[Guest]

One more question for you. Obviously my application of the formula is much
larger and complex than my explanation - but the formula provided works like
a champ.

However, after about the 25th row, the formula stops working. I have
checked my source data, the matches exist, the data to be returned exists,
but the result is a "blank". Any way you might be able to take a peek at
this? or have an idea of what to try??

 

[Pete_UK]

Glad to hear that the formula works.

If it returns blanks then it is not finding a match, so here's a few
things you can check on. Have you copied the first formula all the way
down Sheet2 to cover the amount of data that you have? Do you have any
leading or trailing spaces in column A of either Sheets? Did you make
the changes to the ranges to suit your data?

If you can't fix it then you could send me a slimmed down version of
your file - I don't have XL2007, so you will have to save it as
an .xls file (97-2003 compatible) and send it to:

pashurst <at> auditel.net

Change the obvious.

Pete

 

[Pete_UK]

Lauri sent me a trimmed down version of her workbook - it turned out
that the range she had in the MATCH formula only covered about half
the table, so many items were not being found. Adjusting the range
enabled the appropriate missing data to be displayed.

Pete