Combining two sheets into one

[a94andwi]

Hello.

I got two lists containing part numbers. The same part numbers are on
the two list but each list has some other details that are diffeerent
between the two lists. The two lists are in one workbook on different
sheets named "list1" and "list2".
"List1" contains columns A and B. A = Part number , B = Price.
"List2" contains columns A and B. A=Part number, B=weight.


Part one: I would like to go through each part number on one of the
list and for each part I would like to check if it is in the other list
as well. I guess I need to do a loop of one kind that do this look up.

Part two: In the loop I would like to do this. If the part number exist
in "list2" I would like to copy "List2" column B:s value to "list1"
column C and so on. I would really like know how I can expand this
function as well.


I really hope someone can help me with this. I only need to know the
basics.

/Anders

 

[ben77]

Hi Anders,

Try the following formula in column C of List1:

=VLOOKUP(A1,List2!A:B,2,FALSE)

This will return a #N/A if there are no matches in list 2, if there are
it will return the corresponding value in column B.

If you prefer a blank rather than #N/A for non matches try:

=IF(ISNA(VLOOKUP(A1,List2!A:B,2,FALSE))=TRUE,"",VLOOKUP(A1,List2!A:B,2,FALSE))

(Although this will take twice as long to calculate!)

Hope this helps.

B

 

[ben77]

Heres a bit of code to do this with a macro, though the vlookup wil
probably be less work!


Code
-------------------
Sub test()
Dim intLastrowList1, intLastrowList2

Sheets("List2").Select
intLastrowList2 = Cells(65536, 1).End(xlUp).Row

Sheets("List1").Select
intLastrowList1 = Cells(65536, 1).End(xlUp).Row

For ra = 1 To intLastrowList1
For rb = 1 To intLastrowList2
If Sheets("List1").Cells(ra, 1) = Sheets("List2").Cells(rb, 1) Then Cells(ra, 3) = Sheets("List2").Cells(rb, 2)
Next rb
Next ra

End Su

 

[a94andwi]

Thank you for your help. Maybe I explained it a bit clumpsy.

The scenario I am searching for is this.

Sub test()
For each part in "list1".....
Loop through part numbers in "List2".
If the loop finds the same part number as i
"List1" then....
Copy the value in column B to "List1" colum
C.
End IF
Next part
End sub

Can someone give me the correct syntax for this function?

/Ander

 

[ben77]

I've annotated the macro from before as I did not explain how it works:

Code:
--------------------

Sub test()
Dim intLastrowList1, intLastrowList2

'Determine last row of data in column A in "List 1"
intLastrowList1 = Sheets("List1").Cells(65536, 1).End(xlUp).Row

'Determine last row of data in column A in "List 2"
intLastrowList2 = Sheets("List2").Cells(65536, 1).End(xlUp).Row

'Select the "List1" worksheet
Sheets("List1").Select

'Loop through row 1 to the last row of data in the "List1" worksheet
For ra = 1 To intLastrowList1

'Loop through row 1 to the last row of data in the "List2" worksheet
For rb = 1 To intLastrowList2

'Check if the current row value in column A of "List1" equals
'the current row value in column A of "List2"
'If so copy the contents of column B in "List2" to the
'current row value in column C of "List1"
If Sheets("List1").Cells(ra, 1) = Sheets("List2").Cells(rb, 1) Then Sheets("List1").Cells(ra, 3) = Sheets("List2").Cells(rb, 2)

'Move on the the next row in "List2" to check for a match
Next rb

'Move on the the next row in "List1" once all rows have been checked in "List2"
Next ra

End Sub
--------------------


Hope this helps,

B