Formula or Macro to return a value if cells match

[Justair07]

Hello,

I'm not sure is this is best done via macro or formula. Either way I have not idea how to write either one. I tries a vlookup but did not come close to what I wanted.

I would to take a table of data and if 3 cells in a Row match 3 other cells in a different Row, the return value would be the difference of different cells in the two rows that match.

I attached an example of what I would like the return value to be with another explanation.

I would be very grateful for anyone willing to take a look at this for me.

Thank you,

-Justair

[millz]

Try this, open workbook, enable macros, and click on the button.

[Justair07]

That worked perfect but I just realized that I was asking the question wrong. My data has a slightly different criteria that needs to be identified.

I have cell's titled "Orders", "Footage", "Status". If the status says "Complete" then I need to find the status that says "Pending", and only if the "Footage" and "Orders" match does it return the difference in time.

Attached is an edited sample. Sorry for the confusion.

[millz]

Ok, try this.

[Justair07]

That's awesome! Do you know why the return is not showing the full difference from the dates? It's only showing the difference from the hours, it's excluding the days.

[Justair07]

That's awesome! Do you know why the return is not showing the full difference from the dates? It's only showing the difference from the hours, it's excluding the days.

[Justair07]

Hi millz,

I'm having an issue with the code. For some reason it is only working if I have the "Completed" cell above the "Pending". Otherwise it doesn't work?

Any ideas?

[Justair07]

Is it possible to switch the code so that way the difference is reversed?

Please Login or Register  to view this content.

[Justair07]

Please Login or Register  to view this content.

this worked

[millz]

Hi millz,

I'm having an issue with the code. For some reason it is only working if I have the "Completed" cell above the "Pending". Otherwise it doesn't work?

Any ideas?

Seems like you marked this as solved already. Because initially you only wanted to look for same "status" as per your requirements stated in the first post, then I changed it slightly to look for "Completed" first, then look for "Pending" below that row. If this is an issue still needs to be fixed, let me know again.

[Justair07]

Seems like you marked this as solved already. Because initially you only wanted to look for same "status" as per your requirements stated in the first post, then I changed it slightly to look for "Completed" first, then look for "Pending" below that row. If this is an issue still needs to be fixed, let me know again.

I worked around the issue, it still only works if "Completed" is above "Pending" but I just ran an ascending filter to make all "Completed" always be placed above "Pending".

[Justair07]

Hi millz,

Do you know if there is a way to speed up the code. Maybe a different method perhaps? I tried

Please Login or Register  to view this content.

but is didn't seem to speed anything up.

I currently have the code running through 35,000 lines of data and it take a long time.

Just thought I'd ask and see if you had any ideas.

As always, thanks for the help

[Justair07]

Hi millz,

Do you know if there is a way to speed up the code. Maybe a different method perhaps? I tried

Please Login or Register  to view this content.

but is didn't seem to speed anything up.

I currently have the code running through 35,000 lines of data and it takes a long time.

Just thought I'd ask and see if you had any ideas.

As always, thanks for the help

[AB33]

Since the code loops through a range, for that matter 35k, it tends to be very slow. I do not think adding screen updating will make much difference. Loading the data in to an array is the way forward.
I understood your first request, i.e. matching 2-3 columns, but lost in your request in #3 as you have added some conditions on column A.

[Justair07]

AB33,

Thanks for the reply,

Basically, I have to be able to find cells that all meet certain conditions. For example: If the Cell titled "Status" says "Complete" and the adjacent cell titled "Footage" is the same value as a different row in the "Status" column with the label "Pending" and the "Footage" value is equal to the footage value of the "Complete" row, the time from the "Pending" cell will be subtracted from the "Complete" cell.

I'm sorry if what i said made no sense it was the best way i could think to describe what i'm trying to accomplish.

Thanks for the assistance.