Excel: find, copy, paste in good order sheet2

Closed
Kris - Sep 24, 2009 at 08:03 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 24, 2009 at 10:20 PM
Hello,

I have databse in Sheet1, (columnA: name, B: street, C: post code)
in sheet2 I have in column A: the same name like in Sheet1 but not all and in various order, in next columns (B,C,D,E) I've got other value

I am looking for way how to copy value form cell in column A (sheet2), go to sheet1 find right row, copy 3 cell from this sheet (name, street, post code) and paste it in sheet2 in F,G,H

and do the same with other cell in column A until find value specific value (for example STOP)

to be clear:

sheet1
...|.....A.......|.....B........|....C..........|....D......|.......E.....|..F....|...G....|....H...|
1.|..cable.....|.street1....|..code1.....|............|...............|........|.........|..........|
2.|..Egg.......|..street2...|..code2.....|............|...............|........|.........|..........|
3.|..fabric....|..street3...|..code3.....|............|...............|........|.........|..........|

sheet2
...|.....A.......|.....B.......|....C...........|....D......|.......E......|..F....|...G....|....H...|
1.|.fabric.....|....BFI….|...house.....|..kito.....|...22.00...|........|.........|..........|
2.|.cable.....|....lost.....|…dance.....|..craig...|...21.00....|........|.........|..........|
3.|.Egg.......|....roller...|minimal…|..dale....|...22.00….|........|.........|..........|
4.|.STOP....|

take value form 1A (sheet2): fabric
find it in sheet1 and copy ABC (|.fabric.....|.street3...|..code3.....|)
in to sheet2
after all should it look like this


sheet2
...|.....A.......|.....B........|....C............|....D........|.......E.......|....F.........|......G..........|......H.........|.
1.|.fabric.....|....BFI.....|..house.......|..kito.......|...22.00….|.fabric.....|...street3.....|...code3.....|
2.|.cable.....|....lost......|..dance.......|..craig…..|...21.00….|..cable.....|...street1....|....code1.....|
3.|.Egg.......|....roller...|..minimal…|..dale…...|...22.00…|....Egg.....|...street2.....|...code2.....|
4.|.STOP....|


Thanks!
Kris
Related:

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 24, 2009 at 10:20 PM
suppose headings are in row no.1 both in sheets1 and 2.

in sheet 2 and in cell F2 copy paste this formula

=VLOOKUP($A2,Sheet1!$A$1:$C$100,COLUMN(A$1),0)
copy F2 both down and across to column H

I do not know why you want the names in column F when this is already available in column A

I suppose the total number of rows is <100 if more change "C100" in the formula suitably

read about vlookup function in excel help . it is extremely useful function
0