MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copy data from list based on text value in column A


Posted by warren on October 19, 2000 1:27 PM

I have a AccRec program and need to copy info in rows from one worksheet to another based on a text value entered on second sheet. I also need to be able to print this info in a statement format.
ie. code is entered in sheet2. i need to go to sheet1 find the matching code in column a and copy the rows to associated with that data to sheet2


Posted by thomas venn on October 20, 2000 10:48 AM

try this
note: ` signifies a seperation of cells

sheet 1 data
``a``````b```````c
1`ar1```500````xyz1
2`ar2```600````xyz2
3`ar5```900````xyz3

sheet 2 data
``a``````b
1`ar6```=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A1,Sheet1!$A$1:$A$3,0),1)
2`ar5```=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A2,Sheet1!$A$1:$A$3,0),1)
3`ar2```=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A3,Sheet1!$A$1:$A$3,0),1)

formula for C1
=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A1,Sheet1!$A$1:$A$3,0),2)
formula for d1
=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A1,Sheet1!$A$1:$A$3,0),3)

sheet 2 data RESULTS
``a``````b`````c`````d
1`ar6```#N/A``#N/A``#N/A
2`ar5```ar5```900```xyz3
3`ar2```ar2```600```xyz2


notice that in cells a1, a2, a3, the data matches and returns the result only if it matches exactly, otherwise, it will give you an #N/A result. As for the formatting, you will need to figure out the best format to use for your report.

cheers,

Thomas

(you might also want to learn Vlookup. Vlookup would work also.)

Posted by warren on October 20, 2000 6:42 PM

Thanks thomas for the formulas and hints