Match a Large Data Set Based on Two Criteria

[OilEconomist]

Thanks in advance and I will post feedback on any suggestions. I've just started using this site again as I've needed to VBA Code again and it's just so great how everyone wants to help because of their passion for VBA Coding!

I've looked through the posts and searched the net, but I haven't found anything that I think will quite help me complete this. I did see a post by somebody on this site who has helped me in the past and is very active, but I think it was hard for me to translate it into what I needed.

I am trying to transfer data from the columns of a tab called "Data.Formatted" to "TabA" and then "TabB", etc. The data within "Data.Formatted" is vertical by column and the tabs are opposite, but it's not an easy transpose so I'm matching the

"Data.Formatted" has about 250,000 rows with the following columns:

• A: numbered - not any real significance but just in case any macro wacks it out of order.
• B: Unique ID that is a combination of words and numbers
• C: Category
• D: Date in the format of MM/DD/YYYY (e.g. 1/1/2019, 2/1/2019)
• E through T has the data I'm trying to transfer into the different tabs. Basically each column will get its own tab.

The new tabs will have the Unique IDs going down one column and the dates across the top. I've already written code to have the dates go horizontally starting in column F (basically transposed the dates from "Data.Formatted"). I'm trying to match the date and unique ID and avoid looping as that will bog it down and my computer is a bit slow. I'm looking for the most efficient method.

Any suggestions for code? I already coded to make each tab, have the unique IDs placed in column A and then dates transposed across row 1 starting in column F. Now I would like to match and paste the data from "Data.Formatted."

Once again thanks in advance.

 

[duggie33]

Hi OilEconomist,

You have a lot of data and you are concerned about bogging down your computer. What is the purpose of copying all the data to the "Tab" sheets? You could work with it from your "Data.Formatted" sheet. Depending on what you need to do with it, you might not need to use VBA.

Doug

 

[OilEconomist]

Hi Doug and thanks for your response. I need it formatted to be used elsewhere so I don’t really have a choice at this point.

Any solutions will be much appreciated.

 

[duggie33]

OK. So you have about 250,000 Unique IDs and a Date associated to each Unique ID. There must be multiple Unique IDs for each Date, correct? On the "Tabs" you want to populate cells at the intersection of a Unique ID and Date with data from one of the columns (E through T) on the "Data.Formatted" sheet. Does that sound accurate?

Assuming yes, on the "Tabs" with Unique IDs in A2 and down, Dates in B1 and right I think you can use IF and VLOOKUP. No need for VBA.

The equation to put in each cell would be: =IF(VLOOKUP($A2,Data.Formated!$B$1:$T$30,3,FALSE)=TabA!B$1,VLOOKUP($A2,Data.Formated!$B$1:$T$30,5,FALSE),"")

It would check for unique ID/Date from Data.Formatted to match the Date in row 1 on TabA, if they match it copies the column E (5) data from Data.Formatted to that cell on TabA. You will obviously need to change the ranges to work for your data. The same equation will work on TabB, TabC, etc with change to the column value in the second VLOOKUP. If your data is static on Data.Formatting, you could clear the formulas by doing a copy/pastespecial values to leave only the values.

Hope that helps,

Doug

 

[OilEconomist]

Thanks Doug for your response.

I want to use VBA for this so I can use it on other data sets. Also, there are several other steps prior to this for which I use VBA code. I was avoiding using VLOOKUP in the code as looping would take too much time. I want to try what you sent when I get a chance so I can use it in the future. Will let you know if it works.

I did see somebody posted previously on how to do something similar, but I couldn’t get it to work, and now I can’t find it. I’ll search for it again.

In response to your questions:
Q: There must be multiple Unique IDs for each Date, correct?
A: Each ID will have a date, say for example, spanning from 4/1/2019 to 4/1/2068 and then a Rem., and total.

Q: On the "Tabs" you want to populate cells at the intersection of a Unique ID and Date with data from one of the columns (E through T) on the "Data.Formatted" sheet. Does that sound accurate?
A: Yes

 

[duggie33]

I do not think VLOOKUP would be considered a function that loops. It looks for a specified value in the first column of a specified range and then indexes over a specified number of columns to return the value in that cell.

To do this in VBA, with my knowledge, would require looping through each Unique ID, getting the associated Date, cycling through each "Tab" sheet, and placing the appropriate value in the correct cell (probably with the equivalent of OFFSET AND MATCH). I am not saying that one is better or faster than the other, both should work well. VBA would result in a smaller file than all the formulas but the formulas could most likely be replaced with the values after they have been calculated so that might be a wash.

 

[OilEconomist]

Thanks so much for your help Doug.

When I manually put your formula in with some modifications, it works so I've decided to try to use VBA Code to:
(1) Set all the formulas equal to that (I've renamed "TabA" to "Template.Line.Item.Data") from cell M2 to the LastColumn and LastRow
(2) Calculate
(3) Copy and then Paste Special Values

But I'm getting errors in my code. Any recommendations?

Error on this line "Run-time error '424': Object Required

Sheets(Template.Line.Item.Data).Range("M2:WN" & LastRow).Formula = "= IF( VLOOKUP( $A2, Data.Formatted!B:T, 3, FALSE ) = Template.Line.Item.Data!M$1, VLOOKUP($A2, Data.Formatted!B:T, 5, FALSE ), """" )"

Also, I would luke to change the "WN" which is the last column, but not sure how. Here is the entire code:

Sub Incremental()


    Dim LastRow As Long
    Dim LastColumn As Long
    
    
    
    'Activating the worksheet "Template.Line.Item.Data"
        Worksheets("Template.Line.Item.Data").Activate


    'Find LastRow
        LastRow = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row


        
    'Finding the last column for the NPV Formula which for now has not been adjusted.
        LastColumn = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        MatchCase:=False).Column


        LastColumn = LastColumn - 2
 
    'In this formula I need to modify to include the last column versus using "WN"
        Sheets(Template.Line.Item.Data).Range("M2:WN" & LastRow).Formula = "= IF( VLOOKUP( $A2, Data.Formatted!B:T, 3, FALSE ) = Template.Line.Item.Data!M$1, VLOOKUP($A2, Data.Formatted!B:T, 5, FALSE ), """" )"
        
        Sheets(Template.Line.Item.Data).Calculate
        
        ActiveSheet.Range("M2:WN" & LastRow).Copy
         
        ActiveSheet.Range("M2:WN" & LastRow).PasteSpecial Paste:=xlPasteValues
        
        Application.CutCopyMode = False
            
        ActiveSheet.Cells(2, 6).Select
        
        
    




End Sub

 

[OilEconomist]

Also the formula does not work for anything after the first column. Only works for Column M for some reason. Everywhere else it's blank.

 

[OilEconomist]

Doug,

It looks like it will find the first value and then not find anymore. For example, if I delete the 1/1/2019 value in the "Data.Formatted" tab for that Unique ID, it will then populate 2/1/2019 in the "Template.Line.Item.Data", and if I delete the 2/1/2019 value, it will give me the 3/1/2019, and so on and so on.

 

[duggie33]

In response to post #7:

I believe the sheet name has to be in quotes so “Sheets(“Template.Line.Item.Data”).Range…


There are tons of ways to specify ranges, I know a couple that I use frequently. Maybe “Range(“M2”).Resize(LastRow,LastColumn-??)” would work. Replace the ?? with whatever number is appropriate to get you to columnWN. You could Dim the range and then just refer to it by name too. It looks like it is used in several places.


Just noticed the underlying problem with putting the same formula in all the cells. The formula needs to be different for each cell. For instance “$A2” needs to be “$A3” in all reference cells in row 3, likewise for “M$1”and the columns.This should work if you copy and paste the formulas on the sheet. The alternative is to change your code to put in the correct row and column in each formula…looping again I think.


My suggestion would be to make the column index in the second vlookup reference a cell (say $A$1) and then you change it to pull the data from the different columns on Data.Formatted.


Hope that helps,


Doug