Match two sheets with another based on column and equal space

[tubrak]

hi
I would arrange data in column B into sheets first,second based on matching with sheet BASIC into column A . the space should be one time among the items into column B in sheet first,second based on three columns B,C,D into sheet BASIC . the columns B,C,D into sheet BASIC is each item into column B in sheets first ,second . if can do by macro it will be a great because my data increases with overtime .
first

userform.xlsm
	A	B
1	BATCH	ITEM
2	LK1-100	TR 12-200M45 MM123H K/L
3	LK1-101	TR 200M45 LK H K/L
4	LK1-102	TTR 12200S45 MN 123
5	LK1-103	TR 1425/148V MN 123H K/L
6	LK1-104	TR 200M45 MM 123H K/L
7	LK1-105	TR 2.5M100 * 123H K/L
8	LK1-106	TR 2.5M100**123H K/L
FIRST

second

userform.xlsm
	A	B
1	BATCH	ITEM
2	LK1-100	TR 12-200 M45 MM 123H K/L
3	LK1-101	TR 200M45 LK H K/L
4	LK1-102	TTR 12200S45 MN 123
5	LK1-103	TR 1425 / 148V MN 123H K/L
6	LK1-104	TR 200M45 MM 123H K/L
7	LK1-105	TR 2.5 M100* 123H K/L
8	LK1-106	TR 2.5M100** 123H K/L
9
SECOND

basic

userform.xlsm
	A	B	C	D
1	BATCH	FIRST PART	SECOND PART	THIRD PART
2	LK1-100	TR 12-200M45	MM 123H	K/L
3	LK1-101	TR 200M45	LK H	K/L
4	LK1-102	TTR 12200S45	MN	123
5	LK1-103	TR 1425/148V	MN	123H K/L
6	LK1-104	TR 200M45 MM	123H	K/L
7	LK1-105	TR 2.5M100*	123H	K/L
8	LK1-106	TR 2.5M100**	123H	K/L
BASIC

result
first

userform.xlsm
	A	B
1	BATCH	ITEM
2	LK1-100	TR 12-200M45 MM 123H K/L
3	LK1-101	TR 200M45 LK H K/L
4	LK1-102	TTR 12200S45 MN 123
5	LK1-103	TR 1425/148V MN 123H K/L
6	LK1-104	TR 200M45 MM 123H K/L
7	LK1-105	TR 2.5M100* 123H K/L
8	LK1-106	TR 2.5M100** 123H K/L
FIRST

second

userform.xlsm
	A	B
1	BATCH	ITEM
2	LK1-100	TR 12-200M45 MM 123H K/L
3	LK1-101	TR 200M45 LK H K/L
4	LK1-102	TTR 12200S45 MN 123
5	LK1-103	TR 1425/148V MN 123H K/L
6	LK1-104	TR 200M45 MM 123H K/L
7	LK1-105	TR 2.5M100* 123H K/L
8	LK1-106	TR 2.5M100** 123H K/L
SECOND

 

[Saurabhj]

Hi, please check below. We can use excel formula to get the result.

userform.xlsm.xlsx
	A	B
1	BATCH	ITEM
2	LK1-100	TR 12-200M45 MM 123H K/L
3	LK1-101	TR 200M45 LK H K/L
4	LK1-102	TTR 12200S45 MN 123
5	LK1-103	TR 1425/148V MN 123H K/L
6	LK1-104	TR 200M45 MM 123H K/L
7	LK1-105	TR 2.5M100* 123H K/L
8	LK1-106	TR 2.5M100** 123H K/L
9
first

Cell Formulas
Range	Formula
B2:B8	B2	=CONCATENATE(VLOOKUP(A2,basic!$A$1:$D$8,2,0)," ", VLOOKUP(A2,basic!$A$1:$D$8,3,0), " ", VLOOKUP(A2,basic!$A$1:$D$8,4,0))

userform.xlsm.xlsx
	A	B
1	BATCH	ITEM
2	LK1-100	TR 12-200M45 MM 123H K/L
3	LK1-101	TR 200M45 LK H K/L
4	LK1-102	TTR 12200S45 MN 123
5	LK1-103	TR 1425/148V MN 123H K/L
6	LK1-104	TR 200M45 MM 123H K/L
7	LK1-105	TR 2.5M100* 123H K/L
8	LK1-106	TR 2.5M100** 123H K/L
9
second

Cell Formulas
Range	Formula
B2:B8	B2	=CONCATENATE(VLOOKUP(A2,basic!$A$1:$D$8,2,0)," ", VLOOKUP(A2,basic!$A$1:$D$8,3,0), " ", VLOOKUP(A2,basic!$A$1:$D$8,4,0))

 

[tubrak]

despite of it I would by vba, but thanks for choice the formula.
based on your formula doesn't create one space with comparison my data
some items has two spaces befor or after it , may you check it,please

 

[Saurabhj]

Hi, Yes we can create VBA for this.

Meanwhile using trim can remove the spaces.

=CONCATENATE(TRIM(VLOOKUP(A2,basic!$A$1:$D$8,2,0))," ", TRIM(VLOOKUP(A2,basic!$A$1:$D$8,3,0)), " ", TRIM(VLOOKUP(A2,basic!$A$1:$D$8,4,0)))

 

[tubrak]

thanks for your solution
I will wait for another body to provide me by code .if I don't get it. so I close the thread and accept your answering.

 

[Saurabhj]

Hi,

Please check below code. The code will generate output in Sheet "first"

Option Explicit

Sub fillData()
Dim totRows As Integer, rowno As Integer
Dim foundcell As Range

totRows = Sheets("first").Cells(Rows.Count, 1).End(xlUp).Row

For rowno = 2 To totRows
    Set foundcell = Sheets("basic").Cells.Find(what:=Sheets("first").Range("A" & rowno), After:=Sheets("basic").Cells(1, 1), _
    LookIn:=xlValues, lookat:=xlPart, searchOrder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False, searchformat:=False)
    
    If Not foundcell Is Nothing Then
        Sheets("first").Range("B" & rowno) = Trim(Sheets("basic").Range("B" & foundcell.Row)) & " " & _
        Trim(Sheets("basic").Range("C" & foundcell.Row)) & " " & _
        Trim(Sheets("basic").Range("D" & foundcell.Row))
    End If
Next
End Sub

 

[tubrak]

again thanks and this is great.
do you have any idea to make array like this rr=array(first,second) to implement th code for two sheets instead of repeate the macro again for another sheet ?
I believe the code can be shortened by using array , but I have no idea.

 

[Saurabhj]

Hi,

Use below:

Sub fillData()
Dim totRows As Integer, rowno As Integer
Dim sheetnum As Integer
Dim foundcell As Range
Dim ws(1 To 2) As String

ws(1) = "first"
ws(2) = "second"

For sheetnum = 1 To 2
    For rowno = 2 To Sheets(ws(sheetnum)).Cells(Rows.Count, 1).End(xlUp).Row
        Set foundcell = Sheets("basic").Cells.Find(what:=Sheets(ws(sheetnum)).Range("A" & rowno), After:=Sheets("basic").Cells(1, 1), _
        LookIn:=xlValues, lookat:=xlPart, searchOrder:=xlByRows, _
        searchdirection:=xlNext, MatchCase:=False, searchformat:=False)
       
        If Not foundcell Is Nothing Then
            With Sheets("basic")
                Sheets(ws(sheetnum)).Range("B" & rowno) = Trim(.Range("B" & foundcell.Row)) & " " & _
                Trim(.Range("C" & foundcell.Row)) & " " & _
                Trim(.Range("D" & foundcell.Row))
            End With
        End If
    Next
Next
End Sub

 

[tubrak]

I appreciate for your effort and time.
many thanks for all theses answers