Help with Delimiting Strings

[crobinson661]

I am trying to write a macro to compare two product listings (labeled fruit) for a unique ID between two workbooks; current days output and previous days output.

The products (fruit) are combined in one cell, comma delimited.

Essentially, I want to subtract the previous day's string from the current day's data, leaving only the current day's data for delimiting and association with pricing figures.

I may be overthinking this, but I did a VLOOKUP from the previous day's data in a column and tried using SUBSTITUTE, where I substituted the string from the previous day with (""). This worked ok, except in the case above of "uniqueID1@email.com", where it doesn't isolate the middle value ("grapes")

Afterwards I needed to use SUBSTITUTE for removal of commas as well.

I know I can delimit the previous and current days data and then subtract the range, but it's a daily process and roughly 15K entries a day, so I'm trying to see if there are other options for streamlining that I just don't know about (This is VERY likely)

Any better suggestions?

 

[MrExcel]

Power Query offers a choice to Split Column, By Delimiter, then Split to Rows. This would get you something like
UniqueID1@email.com Apples
UniqueID1@email.com Grapes
UniqueID1@email.com Pears
UniqueID2@email.com Apples
UniqueID2@email.com Pears

Once you had the data from both days split to Rows, then you could do a Merge query, asking for all records in today's table that is not in yesterday's table.

To use Power Query, you would need to be in Excel for Office 365, Excel 2019, or Excel 2016, running in Windows, not a Mac. Does that match your situation?

If so, check out this video showing the steps:

 

[crobinson661]

Thanks for the reply!

I am in Excel for 0365, and that would be fantastic!

Thanks a lot for the assistance!

 

[crobinson661]

That is fantastic!!!!

Thanks so much for the help!!!

 

[crobinson661]

Quick question, I am not getting the "Only Create a Connection Option", it is grayed out?