A tough question from the MrExcel Message Board. There are 1500 rows showing products in a delimited string. Someone might have had "Cherry, Apple" yesterday and today they have "Apple, Banana, Cherry". Can you use a VBA macro to subtract Cherry Apple from Apple Banana Cherry and end up with just Banana?
Today, I end up solving this with Excel Power Query, Split by Delimiter To Rows, and then a Right Anti-Join. The best part: you can refresh the query tomorrow.
Today, I end up solving this with Excel Power Query, Split by Delimiter To Rows, and then a Right Anti-Join. The best part: you can refresh the query tomorrow.
Transcript of the video:
Learn Excel for Mr Excel Podcast Episode 2300, Subtract One Delimited List From Another.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. Well hey from the brand new MrExcel.com message board and one of the questions that came up today was help with delimited strings. Here's an an interesting question.
So we have a whole list of names -- 1500 names here on the left hand side an their order from yesterday.
Raspberry, comma, star fruit and their order from today which has starfruit Raspberry.
But then they've added tangerine and yuzu. How do we try and figure out what's new today? The way I'm going to do this is with Power Query. So I'm going to take yesterday's data. Ctrl+T to make it into a table. Choose "My data has headers" and call that Yesterday.
And then over here Ctrl+T to make it into a table. My table has headers. And call it Today.
We use sort of a new function that came along in Excel 2016. Here Data From a Table or Range in the Get and Transform group.
Back in Excel 2016 it was over here in the third group. So we'll say Data, From a Table or Range.
And I'm going to take the yesterday field, Split Column by Delimiter. And it is smart enough to figure out the delimiter is comma.
We'll choose Comma. At each occurrence of delimiter. And then Advanced Options. Say split to Rows, like that. Click OK.
And we're getting it. But there see their spaces here, so I'm going to right click and Transform and choose Trim. There we are. So now we have the names with the items and instead of Close and Load, I'm going to choose Close and Load To...
Only Create a Connection.
Click OK. Alright, so we're halfway there. Then from the second one: From Table or Range.
Choose that column. Same step.
Split column by delimiter.
The delimiter is going to be a comma. Advanced options. Split into rows. Click OK, Right-click, Transform, Trim.
Good alright? Close and load to Only Create a Connection, click OK. All right, now we have these two queries here in connections.
We will go to Get Data, Combine Queries, and then Merge.
So in the Merge dialogue we are going to say we want to take Yesterday.
And Today. And going to merge first on Name. Ctrl+click on Yesterday. First on Name Ctrl-click on Today.
So the one there, the one there, the two there, and the two there and this is what I want: The...
Rows only in second.
So that way if something such as Andy|Raspberry was in both places then it will not show up.
But I'll get all the items that are new in the second. click OK and Expand this table to get Name and Today. click OK.
Right so Andy added Tangerine, Andy added Yuzu. Barb added Honeydew.
We don't really need these two columns over here, so right click and say remove those columns and then finally Home, Close and Load.
And that should be everything that's new today. Now. The cool thing about Power Query is...
Tomorrow, this data from Today is essentially going to become yesterday's data.
Copy that over and something is going to change, right? So will come out here and add something new like grapefruit.
And then lime, Lemon. Citrus.
Cattle I don't know right, and then Something right. You get the idea. They've added some new stuff. All we would have to do tomorrow to see those new things would be to come here and Data, Queries and Connections.
We get this panel on the right hand side. Refresh and it will show just the new things right?
So well it takes wow. Wow 4 minutes to get it set up the first day. It's just going to be awesome going forward.
Do you like that?
Have you found these videos helpful? Please subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
Thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen. Well hey from the brand new MrExcel.com message board and one of the questions that came up today was help with delimited strings. Here's an an interesting question.
So we have a whole list of names -- 1500 names here on the left hand side an their order from yesterday.
Raspberry, comma, star fruit and their order from today which has starfruit Raspberry.
But then they've added tangerine and yuzu. How do we try and figure out what's new today? The way I'm going to do this is with Power Query. So I'm going to take yesterday's data. Ctrl+T to make it into a table. Choose "My data has headers" and call that Yesterday.
And then over here Ctrl+T to make it into a table. My table has headers. And call it Today.
We use sort of a new function that came along in Excel 2016. Here Data From a Table or Range in the Get and Transform group.
Back in Excel 2016 it was over here in the third group. So we'll say Data, From a Table or Range.
And I'm going to take the yesterday field, Split Column by Delimiter. And it is smart enough to figure out the delimiter is comma.
We'll choose Comma. At each occurrence of delimiter. And then Advanced Options. Say split to Rows, like that. Click OK.
And we're getting it. But there see their spaces here, so I'm going to right click and Transform and choose Trim. There we are. So now we have the names with the items and instead of Close and Load, I'm going to choose Close and Load To...
Only Create a Connection.
Click OK. Alright, so we're halfway there. Then from the second one: From Table or Range.
Choose that column. Same step.
Split column by delimiter.
The delimiter is going to be a comma. Advanced options. Split into rows. Click OK, Right-click, Transform, Trim.
Good alright? Close and load to Only Create a Connection, click OK. All right, now we have these two queries here in connections.
We will go to Get Data, Combine Queries, and then Merge.
So in the Merge dialogue we are going to say we want to take Yesterday.
And Today. And going to merge first on Name. Ctrl+click on Yesterday. First on Name Ctrl-click on Today.
So the one there, the one there, the two there, and the two there and this is what I want: The...
Rows only in second.
So that way if something such as Andy|Raspberry was in both places then it will not show up.
But I'll get all the items that are new in the second. click OK and Expand this table to get Name and Today. click OK.
Right so Andy added Tangerine, Andy added Yuzu. Barb added Honeydew.
We don't really need these two columns over here, so right click and say remove those columns and then finally Home, Close and Load.
And that should be everything that's new today. Now. The cool thing about Power Query is...
Tomorrow, this data from Today is essentially going to become yesterday's data.
Copy that over and something is going to change, right? So will come out here and add something new like grapefruit.
And then lime, Lemon. Citrus.
Cattle I don't know right, and then Something right. You get the idea. They've added some new stuff. All we would have to do tomorrow to see those new things would be to come here and Data, Queries and Connections.
We get this panel on the right hand side. Refresh and it will show just the new things right?
So well it takes wow. Wow 4 minutes to get it set up the first day. It's just going to be awesome going forward.
Do you like that?
Have you found these videos helpful? Please subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
Thank you for stopping by. We'll see you next time for another netcast from MrExcel.
