I want to copy one cell value from sheet 1 to sheet 2 conditionall

[RobertoB]

Hi I have a couple of worksheets that are making me crazy, and I will
appreciate any idea you can give me: The first worksheet looks as follows:

WKS1:
cust name postcode custnumber
AB me1 4qq 12345
Abc me4 6nu 12346
abcd me3 8fg 12347
abcde me4 1ff 12348

And another that looks as follows:

WKS2
custname postcode custnumber
Abc me4 6nu
AB me1 4qq
abcd me3 8fg
abcde me4 1ff


As you can see:

1) The values in column custname are the same BUT they are NOT in the same
order.

2) The custnumber is empty in WKS2.

So what I want to do is:

from WKS2 pickup the values from custname (one by one) and compare them to
the same column in WKS1, if the value match then vaildate if the value in
column postcode from WKS1 (is WKS1 no misstake here) is the same as postcode
in WKS2. If so copy the value from the correspondent custnumber from WKS1 to
WKS2.

That is the must have if possible.

The nice to have will be to also copy the cell format from WKS1 to WKS2, as
some custnumber values are in red, while others are in black.

THANK YOU for any idea you can share.

Rob.

 

[Don Guillett]

Sub copynumstoothersht()
Sheets("wks1").Select
For i = 2 To Cells(Rows.Count, "a").End(xlUp).Row
With Sheets("wks2")
mr = .Cells.Find(What:=Cells(i, "a"), After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row
Cells(i, 4).Copy
..Cells(mr, 4).PasteSpecial Paste:=xlPasteAllExceptBorders
Application.CutCopyMode = False
End With
Next i
End Sub

 

[ShaneDevenshire]

Hi,

Here is a formula approach:

Assume your data start in cell A1 of Sheet1 and Sheet2 so you want to copy
the customer number from sheet1 to sheet2 if the name and post code match:
In cell C2, under the title, array enter the following formula:

=IF(ISNA(MATCH(A2&B2,Sheet1!A$2:A$5&Sheet1!B$2:B$5,0)),"",OFFSET(Sheet1!C$1,MATCH(A2&B2,Sheet1!A$2:A$5&Sheet1!B$2:B$5,0),0))

To enter this as an array press Shift+Ctrl+Enter not Enter.

copy the formula down as far as necessary.