Excel Forum

Not Sure VlookUP | XLookUP | Filter | INDEX MATCH

Pam B

Active Member

Members

Forum Posts: 4

Member Since:
May 25, 2024

Offline

July 11, 2024 - 5:39 am

Good afternoon, Mynda

Hope all is well with you.

Do you think that you can help me with this issue? I won’t be able to provide sample data. Sorry.

I have 2 Worksheets. One has over 150K records. Other has 1500 records. For the one that has 150K Records, I need to give a Filter on Column P and Choose a Specific Value let us call it PA. What I did is I copied and pasted these filtered records on a new Sheet so now I have roughly 40K Records with the filtered cells.

Please let me know if that is not the right way to do?

Column A in the Filtered Master Sheet(40K) is LastNameAndFirstName. I converted it to a Table Named CE.

Column X in Other Sheets Let us call it Second Sheet and the Table Named MR is LastNameAndFirstName.

Column A from table CE = Column X from table ME (Both have LastNameAndFirstName)

My Goal is to find these 2 things.

I need to make sure that 1500 Records from Table MR exist in Master Sheet Table CE.
If they exist, then I need to check if Col R (Job Start Date) From Table MR has the same date as in Col AG (Join Date) from Table CE.

Col R = Col AG (From 2 Tables CE and MR should have the same dates, if not then it should highlight or some message)

Note: In these columns, some cells have dates, and some are blank.

Thanks,

Pam

Mynda Treacy

Admin

Forum Posts: 4782

Member Since:
July 16, 2010

Offline

July 13, 2024 - 9:35 am

Hi Pam,

You can easily automate all of this with Power Query. Including the initial task of extracting the 40k rows of PA data. Then any new data or updates to the original data can be picked up with the click of a button.

It's hard to be more specific without an example file, even a small subset of anonymised data. If you want to mock one up then we can give you a proof of concept solution so you can see how it could work.

Hope that points you in the right direction.

Mynda

Pam B

Active Member

Members

Forum Posts: 4

Member Since:
May 25, 2024

Offline

July 14, 2024 - 10:52 am

Hi Mynda,

Thank You! I had used XLOOKUP and I was able to do it successfully. I got exactly what I wanted but the way our company setting is done caused issues because it replaced all my links with SharePoint Links and all the calculations I did start showing #REF! One of the reports I run and the other one I get from Vendors and the report I received from Vendors was causing a lot of issues since it had some VBA code and our company blocks VBA Code I don't know why but where I should be seeing my XLOOKUP changed to SharePoint Links and all values in 6 to 7 columns I had worked on started giving me an error of #REF!.

Thank you for advising me about Power Query. I will try this on Monday and will let you know if I can do it or not since at home, I don't have a Windows laptop and on Mac Power Query does not work. In case I am not able to do it with Power Query then I will do the same thing I had done with XLOOKUP and then save it with values only and not XLOOKUP Formulas. The helpdesk was also not able to restore my report.

Thank you very much!

Pam