Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare two columns on two sheets and copy associated data from one sheet to the other?
I have two sheets of data and Column A on both sheets has the same
type data (numbers) some numbers are the same on both sheets in Column A, some are different, the numbers are not in the same order on both sheets. Sheet1 also has a value in Columns Q & R in the same row that is associated with Column A. I want to copy the value in Column Q&R to Sheet 2 if the same number in column A exists on sheet 2. Can anyone help me with this? I have been trying to the use the VLOOKUP function , but am not having any luck. Sheet 1 Column A Column Q Column R row1 5-123 test_01 details_and_code row2 2-657 test_06 code row3 5-1245 test_08 writing Sheet 2 Column A Column Q Column R row1 5-123 row2 4-4456 row3 5-1245 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare two columns on two sheets and copy associated data from one sheet to the other?
=VLOOKUP(A2,'Sheet1'!A:Z,17,0) --- for column Q
=VLOOKUP(A2,'Sheet1'!A:Z,18,0) --- for column R HTH Kostis Vezerides On Jun 22, 4:51 pm, wrote: I have two sheets of data and Column A on both sheets has the same type data (numbers) some numbers are the same on both sheets in Column A, some are different, the numbers are not in the same order on both sheets. Sheet1 also has a value in Columns Q & R in the same row that is associated with Column A. I want to copy the value in Column Q&R to Sheet 2 if the same number in column A exists on sheet 2. Can anyone help me with this? I have been trying to the use the VLOOKUP function , but am not having any luck. Sheet 1 Column A Column Q Column R row1 5-123 test_01 details_and_code row2 2-657 test_06 code row3 5-1245 test_08 writing Sheet 2 Column A Column Q Column R row1 5-123 row2 4-4456 row3 5-1245 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare two columns on two sheets and copy associated data from one sheet to the other?
Hi,
=VLOOKUP($A1,Sheet1!$A$1:$R$1000,COLUMN(),0) in cell Q1 should work. Align last row (above: 1000) with your data range. Formula can be copied downwards and to the right with excel adjusting the cell references itself. =IF(ISERROR(VLOOKUP($A1,Sheet1!$A$1:$R$1000,COLUMN (), 0)),"",VLOOKUP($A1,Sheet1!$A$1:$R$1000,COLUMN(),0) ) will return empty cells instead of error n/a for any number not to be found on Sheet1. Ingolf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy data from multiple sheets into new sheet | Excel Worksheet Functions | |||
compare two columns data and bring them to another work sheet | Excel Discussion (Misc queries) | |||
copy data from various sheets into one sheet | Excel Worksheet Functions | |||
copy data in one sheet to multiple sheets in same workbook | Excel Worksheet Functions | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions |