matching data from one excel sheet to another

[rk68]

Hi,
I have 2 excel sheet.
I have to match value from 1st excel sheet to another excel using multiple keys

1st excel sheet

type No. value
CAR PETROL AB1 112
CAR GAS CC4 145
BUS GAS JY7 174

2nd excel sheet

type No. value
CARPETROL AB1
CARGAS CC4
BUSPETROL MH4

I have to populate the value from 1st worksheet to 2nd worksheet matching both type & no.
Only 2 records are matching in both excel sheet, but how to populate using formula. The key is type & No.

TIA,

Regards,
RAJ

 

[Andrzejek]

> Only 2 records are matching in both excel sheet

No, they do not:[pre]
CAR PETROL <> CARPETROL
CAR GAS <> CARGAS [/pre]

Unless there are some other rules that you do not mention here....

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

With Andy's observation, formulas are not best solution. I would use power query:
1. make tables from both datasets in sheet 1 and sheet 2,
2. power query (1) from table 1, preprocess: remove spaces in col. 1, trim col. 2, values in col. 3. Create connection only,
3. power query (2) from table 2, preprocess: add new column (3) from col. 1, column, remove spaces, trim col. 2. Merge query (1) with proper join type and fields (T1.Col1=T2.Col2, T1.Col2=T2.Col2), expand value, remove helper column (without spaces. Output to workbook.

Fast, no excel formulas, only refresh required when data change.

combo

 

[Andrzejek]

Let's 'translate' it:

1st excel sheet
[pre]
type No. value
CAR PETROL AB1 112
CAR GAS CC4 145
BUS GAS JY7 174[/pre]

2nd excel sheet
[pre]
type No. value
CARPETROL AB1 [red]112[/red]
CARGAS CC4 [red]145[/red]
BUSPETROL MH4[/pre]

Get [red]RED[/red] values from 1st excel sheet

I would do it with a simple (a few lines of) VBA...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[rk68]

Thank you Andy & Combo.

@Andy - there are no other rules.
I had thought about what Combo mentioned but is more of a manual process and I don't want to do this as there are multiple worksheets where I need to do this. Hence was thinking of some function which could be used across the worksheets.

As Andy suggested will look into VBA option.

Thanks once again.

 

[Andrzejek]

If your data looks like this:

This VBA code:

Option Explicit

Sub rk68()
Dim S1 As Worksheet
Dim S2 As Worksheet
Dim R1 As Integer
Dim R2 As Integer

Set S1 = ThisWorkbook.Sheets(1)
Set S2 = ThisWorkbook.Sheets(2)
R2 = 2

Do While Not IsEmpty(S2.Cells(R2, 1).Value)
    R1 = 2
    Do While Not IsEmpty(S1.Cells(R1, 1).Value)
        If (Replace(S1.Cells(R1, 1).Value, " ", "") = S2.Cells(R2, 1).Value) And _
            (S1.Cells(R1, 2).Value = S2.Cells(R2, 2).Value) Then
            
            S2.Cells(R2, 3).Value = S1.Cells(R1, 3).Value
            Exit Do
        End If
        R1 = R1 + 1
    Loop
    R2 = R2 + 1
Loop

End Sub

will give you in Sheet2:[blue][pre]
C
value
112
145[/pre][/blue]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[rk68]

Thanks a ton Andy.
It helped.