Concatinating two spreadsheets by a column

G

Georgi

Hi,

I need to find a way to concatenate two different spreadsheets by a
column - I have two spreadsheets with slightly different information,
but with a column that can be used as a key. What I need is to find a
way to collate the information in the two columns, and if a
coincidence is found to transfer some of the cells from the same line
as where the coincidence is found from the first spreadsheet into the
other. Also the lines where there is no coincidence should be removed.
Example :

spreadsheet 1 :

ABV 111
BBB 222
VVV 333
GGG 333
DDD 444
ZZZ 567
OOO 452

Spreadsheet 2 :
AAA 432
DDD 324
BBB 342
VVV 432
OOO 642
ZZZ 7878

The first column is the key, so spreadsheet 3 should be :

BBB 222 342
VVV 333 432
OOO 642 452
ZZZ 567 7878


Thanks in advance !
 
A

Ayo

Hey Georgi, if you send the file to me at (e-mail address removed) I can get this
back to you in about 15 to 30 mins. It will be a little lengthy and confusing
to explain the process here. This way you can see the code when you get the
file back.
 
G

Georgi

Hi,

I need to find a way to concatenate two different spreadsheets by a
column - I have two spreadsheets with slightly different information,
but with a column that can be used as a key. What I need is to find a
way to collate the information in the two columns, and if a
coincidence is found to transfer some of the cells from the same line
as where the coincidence is found from the first spreadsheet into the
other. Also the lines where there is no coincidence should be removed.
Example :

spreadsheet 1 :

ABV 111
BBB 222
VVV 333
GGG 333
DDD 444
ZZZ 567
OOO 452

Spreadsheet 2 :
AAA 432
DDD 324
BBB 342
VVV 432
OOO 642
ZZZ 7878

The first column is the key, so spreadsheet 3 should be :

BBB 222 342
VVV 333 432
OOO 642 452
ZZZ 567 7878

Thanks in advance !
Click to expand...


Hi,

Ayo helped me great with this one, but now I need i slight change in
the VBA and ( apparently ) I cannot do it on my own. Here is the code
he provided :

Private Sub cmdUpdate_Click()
Dim sheet1WS As Worksheet, sheet2WS As Worksheet, tableCombWS As
Worksheet
Dim table1lastRow1 As Integer, table1lastRow2 As Integer
Dim rngtable1data1 As Range, rngtable1data2 As Range, rngtable2data As
Range
Dim c1 As Range, c2 As Range
Dim currRow As Integer, table1currRow1 As Integer, table1currRow2 As
Integer

Set table1WS = Worksheets("table1")
table1lastRow1 = table1WS.Range("E65536").End(xlUp).Row
table1lastRow2 = table1WS.Range("R65536").End(xlUp).Row
Set rngtable1data1 = table1WS.Range("$E$2:$E$" & table1lastRow1 &
"")
Set rngtable1data2 = table1WS.Range("$R$2:$R$" & table1lastRow2 &
"")

Set table2WS = Worksheets("table2")
'table2lastRow = table2WS.Range("D65536").End(xlUp).Row
'Set rngtable2data = table2WS.Range("$E$2:$E$" & table2lastRow & "")

currRow = 2
For Each c1 In rngtable1data1.Cells
For Each c2 In rngtable1data2.Cells
If c1.Text = c2.Text Then
table1currRow1 = c1.Row
table1currRow2 = c2.Row
table2WS.Range("A" & currRow) = c1.Offset(0, -4)
table2WS.Range("B" & currRow) = c1.Offset(0, -3)
table2WS.Range("C" & currRow) = c1.Offset(0, -2)
table2WS.Range("D" & currRow) = c1.Offset(0, -1)
table2WS.Range("E" & currRow) = c1
table2WS.Range("F" & currRow) = c1.Offset(0, 1)
table2WS.Range("G" & currRow) = c1.Offset(0, 2)
table2WS.Range("H" & currRow) = c1.Offset(0, 3)
table2WS.Range("I" & currRow) = c1.Offset(0, 4)
table2WS.Range("J" & currRow) = c1.Offset(0, 5)
table2WS.Range("K" & currRow) = c1.Offset(0, 6)
table2WS.Range("L" & currRow) = c1.Offset(0, 7)
table2WS.Range("M" & currRow) = c1.Offset(0, 8)
table2WS.Range("N" & currRow) = c1.Offset(0, 9)

table2WS.Range("O" & currRow) = c2.Offset(0, 4)
table2WS.Range("P" & currRow) = c2.Offset(0, 5)
table2WS.Range("Q" & currRow) = c2.Offset(0, 6)
table2WS.Range("R" & currRow) = c2.Offset(0, 7)
table2WS.Range("S" & currRow) = c2.Offset(0, 8)
table2WS.Range("T" & currRow) = c2.Offset(0, 9)
table2WS.Range("U" & currRow) = c2.Offset(0, 10)
table2WS.Range("V" & currRow) = c2.Offset(0, 11)
table2WS.Range("W" & currRow) = c2.Offset(0, 12)
table2WS.Range("X" & currRow) = c2.Offset(0, 13)
table2WS.Range("Y" & currRow) = c2.Offset(0, 14)
table2WS.Range("Z" & currRow) = c2.Offset(0, 15)
currRow = currRow + 1
Exit For
End If
Next c2
Next c1
End Sub

This one presumes that the info is in two sheets in one document, and
it makes a new sheet with the new data. As you can see from the code
it uses column E in the first sheet and column R from the second as a
key, and creates a new table with columns A-N from first sheet plus
the relative V-AG columns from second sheet.

Can you please help me, find a way to make it work with two sheets
where C is key in the first one and B in the second, and what I need
to do is create a new table with the whole second sheet, plus the
relative information from A cell ?

I know that it sounds stupid, but I'm rookie, and cannot find a way to
do it.

Thank you very much :)
 
G

Georgi

Hi,

Ayo helped me great with this one, but now I need i slight change in
the VBA and ( apparently ) I cannot do it on my own. Here is the code
he provided :

Private Sub cmdUpdate_Click()
Dim sheet1WS As Worksheet, sheet2WS As Worksheet, tableCombWS As
Worksheet
Dim table1lastRow1 As Integer, table1lastRow2 As Integer
Dim rngtable1data1 As Range, rngtable1data2 As Range, rngtable2data As
Range
Dim c1 As Range, c2 As Range
Dim currRow As Integer, table1currRow1 As Integer, table1currRow2 As
Integer

Set table1WS = Worksheets("table1")
 table1lastRow1 = table1WS.Range("E65536").End(xlUp).Row
 table1lastRow2 = table1WS.Range("R65536").End(xlUp).Row
 Set rngtable1data1 = table1WS.Range("$E$2:$E$" & table1lastRow1 &
"")
 Set rngtable1data2 = table1WS.Range("$R$2:$R$" & table1lastRow2 &
"")

Set table2WS = Worksheets("table2")
 'table2lastRow = table2WS.Range("D65536").End(xlUp).Row
 'Set rngtable2data = table2WS.Range("$E$2:$E$" & table2lastRow & "")

currRow = 2
For Each c1 In rngtable1data1.Cells
   For Each c2 In rngtable1data2.Cells
       If c1.Text = c2.Text Then
           table1currRow1 = c1.Row
           table1currRow2 = c2.Row
           table2WS.Range("A" & currRow) = c1.Offset(0, -4)
           table2WS.Range("B" & currRow) = c1.Offset(0, -3)
           table2WS.Range("C" & currRow) = c1.Offset(0, -2)
           table2WS.Range("D" & currRow) = c1.Offset(0, -1)
           table2WS.Range("E" & currRow) = c1
           table2WS.Range("F" & currRow) = c1.Offset(0, 1)
           table2WS.Range("G" & currRow) = c1.Offset(0, 2)
           table2WS.Range("H" & currRow) = c1.Offset(0, 3)
           table2WS.Range("I" & currRow) = c1.Offset(0, 4)
           table2WS.Range("J" & currRow) = c1.Offset(0, 5)
           table2WS.Range("K" & currRow) = c1.Offset(0, 6)
           table2WS.Range("L" & currRow) = c1.Offset(0, 7)
           table2WS.Range("M" & currRow) = c1.Offset(0, 8)
           table2WS.Range("N" & currRow) = c1.Offset(0, 9)

           table2WS.Range("O" & currRow) = c2.Offset(0, 4)
           table2WS.Range("P" & currRow) = c2.Offset(0, 5)
           table2WS.Range("Q" & currRow) = c2.Offset(0, 6)
           table2WS.Range("R" & currRow) = c2.Offset(0, 7)
           table2WS.Range("S" & currRow) = c2.Offset(0, 8)
           table2WS.Range("T" & currRow) = c2.Offset(0, 9)
           table2WS.Range("U" & currRow) = c2.Offset(0, 10)
           table2WS.Range("V" & currRow) = c2.Offset(0, 11)
           table2WS.Range("W" & currRow) = c2.Offset(0, 12)
           table2WS.Range("X" & currRow) = c2.Offset(0, 13)
           table2WS.Range("Y" & currRow) = c2.Offset(0, 14)
           table2WS.Range("Z" & currRow) = c2.Offset(0, 15)
           currRow = currRow + 1
           Exit For
       End If
   Next c2
Next c1
End Sub

This one presumes that the info is in two sheets in one document, and
it makes a new sheet with the new data. As you can see from the code
it uses column E in the first sheet and column R from the second as a
key, and creates a new table with columns A-N from first sheet plus
the relative V-AG columns from second sheet.

Can you please help me, find a way to make it work with two sheets
where C is key in the first one and B in the second, and what I need
to do is create a new table with the whole second sheet, plus the
relative information from A cell ?

I know that it sounds stupid, but I'm rookie, and cannot find a way to
do it.

Thank you very much :)
Click to expand...

P.S. Sheet2 is A-T, so the new table should be A-T from the second
table + A from the first :)
I don't know whether it's relative, but the sheets are very big
( sheet1 is 40-45k lines, sheet2 is 2-3k )
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concatenating two spreadsheets by a column 1
row comparison 5
Repeat Values in Column A, Break and Sum 2
comparing a column in two separate spreadsheets 2
Group by and count queries 1
Sort and match two columns without touching a third column...I'm anovice... 3
combining two spreadsheets with a common field 1
HOW DO I COMBINE two spreadsheets into one by names in column a 3

Top