Matching Data
Posted by Denise on December 06, 2000 7:41 PM
I am stumped! Here is my problem:
I have one sheet of data with two columns-
Column 1 Column 2
111 butter
112 margarine
112 bread
112 jam
113 jelly
On sheet 2 says
111
112
113
I would like to match butter, margarine, bread, jam
and jelly on sheet 2 by number. The problem is if I do
a vlookup, it only picks up the first item that matches
the number.
Ultimately I want Sheet 2 to say:
column1 column 2 column 3 column 4
111 butter
112 margarine bread jam
113 jelly
Thanks for any advice you can give!
Posted by Celia on December 07, 2000 12:56 AM
Denise
There is probably a better way but you may like to try this :-
I have assumed that the data starts in cell A2 on both sheets.
For the formulas to work, the row immediately above the first data row on Sheet1 must be blank.
In cell C2 of Sheet1 enter the following formula:-
=IF(A2<>A1,1,C1+1)
Fill it down to the last row of data.
In cell B2 of Sheet2 enter the following array formula (enter with Ctrl+Shift+Enter) :-
=IF(ISNA(INDEX(Sheet1!$B$2:$B$100,MATCH(COLUMN()-1&Sheet1!$D2,Sheet1!$C$2:$C$100&Sheet1!$A$2:$A$100,0))),"",INDEX(Sheet1!$B$2:$B$100,MATCH(COLUMN()-1&Sheet1!$D2,Sheet1!$C$2:$C$100&Sheet1!$A$2:$A$100,0)))
Fill it across the columns and down the rows as far as necessary.
The formula as written allows for up to 100 rows of data.
Celia
Posted by Aladin Akyurek on December 09, 2000 4:30 AM
It seems I have produced an alternative answer to your question. It's rather lengthy to expose here. If you're interested, please send your e-mail address to get the Excel file containing the answer.
Aladin
Posted by Denise on December 11, 2000 7:24 AM
Aladin, I am interested in what your answer may be.
Thanks
Posted by Celia on December 11, 2000 8:32 AM
Aladin
I'm interested too.
Celia
Posted by Celia on December 11, 2000 5:59 PM
Correction
Thanks to Aladin, I've realised there is an error in the formula posted. It should be :-
=IF(ISNA(INDEX(Sheet1!$B$2:$B$100,MATCH(COLUMN()-1&$A2,Sheet1!$C$2:$C$100&Sh
eet1!$A$2:$A$100,0))),"",INDEX(Sheet1!$B$2:$B$100,MATCH(COLUMN()-1&$A2,Sheet
1!$C$2:$C$100&Sheet1!$A$2:$A$100,0)))
Also, I should have mentioned that the data on Sheet1 has to be sorted by column A.
Celia
Posted by trcolw on January 05, 2001 3:28 PM
Could I also please get a copy of your solution.
Thanks!
Posted by Jenny Scarbrough on January 18, 2001 9:23 AM
If you could please send me a solution to this problem,
I would appreciate it as well. I am trying to do the same thing.
Posted by chaimw on January 18, 2001 1:11 PM
Re: Correction
Posted by Art F on January 29, 2001 10:22 AM
I have done this many times and I use an imbedded if statement. On page two in cell B1 write
=if(A1=lookup(A1,Sheet1!$A$1:$A$7),lookup(a1,Sheet1!$A$1:$A$7,Sheet1!$B$1:$B$7),"")
Make sure you have all of the column information in a & b covered. This will look at the data and see if there is a match then it will bring over the data. If there is no match it will leave it blank
Art
