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.

 

[OilEconomist]

Thanks Doug.

Also, as I stated in Post #8 , the formula does not work for anything past the first column as I entered it manually versus through VBA.

If go back and delete the Unique ID for 1/1/2019, it will then only give me the 2/1/2019 value, but nothing after. If I delete the Unique ID for 1/1/2019, and 2/1/2019, it will then only give me the return value for 3/1/2019, but nothing before or after. Seems like it's only picking up the first value. Any suggestions on how to change it.

Here is the final equation I used:

= IF( VLOOKUP( $A2, Data.Formatted!B:T, 3, FALSE ) = Template.Line.Item.Data!M$1, VLOOKUP($A2, Data.Formatted!B:T, 5, FALSE ), "" )

Thanks!

 

[duggie33]

Sometimes you have to troubleshoot why a formula does not work as expected. When copied to adjacent cells, what changes? I am guessing that "B:T" should have $'s.

 

[OilEconomist]

Apologies in the VBA Code I had it as:

= IF( VLOOKUP( $A2, Data.Formatted!B:T, 3, FALSE ) = Template.Line.Item.Data!M$1, VLOOKUP($A2, Data.Formatted!B:T, 5, FALSE ), "" )

When I pasted it in the sheet, I did as follows and I also troubleshooted it as much as I could.

= IF( VLOOKUP( $A2, Data.Formatted!$B:$T, 3, FALSE ) = Template.Line.Item.Data!M$1, VLOOKUP($A2, Data.Formatted!$B:$T, 5, FALSE ), "" )

 

[duggie33]

Do you follow how the formula is supposed to work? There is a chance that I misunderstood your needs and have the formula returning the wrong data.

My understanding is that you are placing the formula in Template.Line.Item.Data!M2. It is to compare the date in M1 to the date associated with the Unique ID (A2) on the Data.Formatted sheet. If the dates match, it returns the value four columns to the right of the Unique ID. If it does not match it returns and empty string (""). Since there is only one date associated to a Unique ID, there will only be one cell in that row that gets populated with a value; the rest will be empty. When the formula is copied to N2, it compares the date in N1 to the Unique ID date. When copied to M3, it compares the date in M1 to the Unique ID date in A3.

If that is not the case, you will have to adjust the formula to match your data.

Doug

 

[OilEconomist]

Apologies Doug as I don’t totally understand the formula and hopefully you’re not getting frustrated.

= IF( VLOOKUP( $A2, Data.Formatted!$B:$T, 3, FALSE ) = Template.Line.Item.Data!M$1, VLOOKUP($A2, Data.Formatted!$B:$T, 5, FALSE ), "" )

I tried another formula, but the calculations were taking too long. It is:

=INDEX( Data.Formatted!$F$2:$F$208024, SUMPRODUCT(( Data.Formatted!$B$2:$B$208024 = $A2)* ( Data.Formatted!$D$2:$D$208024 = M$1) = ROW( Data.Formatted!$F$2:$F$208024 )),0)

Destination: “Template.Line.Item.Data”

• Cells A2 through A416 have the Unique IDs.
• Columns M through WP have the dates.
• So I would like to obtain the corresponding values from “Data.Formatted” and paste throughout cells M2 through WP416

Source Data: “Data.Formatted”

• Cells B2 through B208,024 have the Unique IDs
• Cells D2 through D208,024 have the dates
• Columns E through T have the data I want to retrieve where I will make one tab for each column I retrieve. In my example, I have been focusing on one column for ease as I can just change the column for the other tabs.

I am placing the formula within cell M2 of “Template.Line.Item.Data”. Within that same tab, it uses the unique ID from cell A1 and date from cell M1 to obtain a value from “Data.Formatted” and then I go all the way down column M for about 416 cells and then across the entire sheet to Column WP.

 

[duggie33]

The formula should work, it worked it my testing on generic data.

VLOOKUP is a great function for what it does, it has its limitations but can be very useful. There are tons of resource on the internet about how it is used. It goes top to bottom in the first column of the lookup array, looking for the lookup value, when it is located it indexes to the column indicated and returns that value. For instance, say the lookup value is UniqueID28, it finds it in cell B29 of your Data.Formatted sheet, it indexes to the third column of the lookup array (B=1, C=2,D=3 because your lookup array started in B)...there in D29 it finds the date for the UniqueID28. The "FALSE" tells it to return an exact match only. If you copy Unique IDs from one sheet to another, you should always have an exact match. If dates match per the IF statement, the second VLOOKUP returns the value in F29 (5th column of lookup array).

Because you only have one date for each Unique ID, there will only be one value in each row so there will be a lot of empty cells after you copy the formula. Put the formula in M2 and test it by copying it to the cell in row 2 with the date that corresponds to the Unique ID on the Data.Formatted sheet.

I am guessing you know this but I will say it anyway...the "$" in the formula freezes value to the right of it when copying and pasting formulas. $A2 in a formula copied to the right will stay $A2 but when copied down to row three it will change to $A3.

Hopefully that helps,

Doug