VB for Excel Please help!!

[mdrollas]

To all the anand programming wizards. I do not know VB code but is in need of help on data filtering/matching. I have 2 very big excel workbooks with a few thousand lines and am trying to do some filtering/matching. Example below.

The two workbooks I have contains same data in column A and also column D from the first data set is the same as column B in the 2nd data set. I am trying to add column C and D from the 2nd data set to the 1st data set and need to make sure they match up to column D from 1st set and B from 2nd set. It is quite strenuous to do this manually, any input would be greatly appreciated.

Thanks!


A1 2 3 100A
A1 2 3 100B
A1 2 3 100C
A1 2 3 100D


A1 100A AA BA
A1 100B AB BB
A1 100C AC BC
A1 100D AD BD

 

[mayest]

Do you have to do this with VBA? If not, I would suggest that you use a combination of Index() and Match(). Take a look at my response to another question here:

http://forums.anandtech.com/me...id=33&threadid=2171718

You should be able to modify that to fit your data ranges and then just copy the formulas down. If you don't want the formulas to stay live, you can then do a Copy and Paste Special Values.

 

[KLin]

This is easy to do in microsoft access. You can even copy/paste excel spreadsheets into an access database and they would automatically have tables created for each spreadsheet. Then run a query like

SELECT Dataset1.A, Dataset1.B, Dataset1.C, Dataset1.D, DataSet2.C, Dataset2.D from Dataset1 INNER JOIN Dataset2 on DataSet1.D = Dataset2.B

 

[Duwelon]

Originally posted by: mdrollas
To all the anand programming wizards. I do not know VB code but is in need of help on data filtering/matching. I have 2 very big excel workbooks with a few thousand lines and am trying to do some filtering/matching. Example below.

The two workbooks I have contains same data in column A and also column D from the first data set is the same as column B in the 2nd data set. I am trying to add column C and D from the 2nd data set to the 1st data set and need to make sure they match up to column D from 1st set and B from 2nd set. It is quite strenuous to do this manually, any input would be greatly appreciated.

Thanks!


A1 2 3 100A
A1 2 3 100B
A1 2 3 100C
A1 2 3 100D


A1 100A AA BA
A1 100B AB BB
A1 100C AC BC
A1 100D AD BD

try this macro, but change the sheet names to match your worksheets.

Dim worksheet2(10000,2)

Sub mergesheets()

Worksheets("sheet2").Activate

x = 1
Do While Cells(x, 1) <> "" 'if you have blank rows you should sort your sheet so you don't
worksheet2(x - 1, 0) = UCase(Cells(x, 2))
worksheet2(x - 1, 1) = UCase(Cells(x, 3))
worksheet2(x - 1, 2) = UCase(Cells(x, 4))
x = x + 1
Loop

'now the second worksheet's B C and D columns are loaded into a 2 dimensional array

Worksheets("sheet1").Activate

x = 1
Do While Cells(x, 1) <> ""
z = 0
Do While worksheet2(z, 0) <> ""
If worksheet2(z, 0) = Ucase(Cells(x, 4)) Then
Cells(x, 5) = worksheet2(z, 1)
Cells(x, 6) = worksheet2(z, 2)
Exit Do
End if
z = z + 1
Loop
x = x + 1
Loop

End sub

 

[JJChicken]

Originally posted by: mayest
Do you have to do this with VBA? If not, I would suggest that you use a combination of Index() and Match(). Take a look at my response to another question here:

http://forums.anandtech.com/me...id=33&threadid=2171718

You should be able to modify that to fit your data ranges and then just copy the formulas down. If you don't want the formulas to stay live, you can then do a Copy and Paste Special Values.

Yep I was thinking the same thing. One thing to note is that with index/match, problems can arise if theres multiple instance of a cell's contents in column D.

 

[mdrollas]

Thanks for all the guidance! Will give each option a try.