You have a product category in A and then 6 products in B:G. The goal is to get 7 rows for each category, first with the category name, and then 6 rows concatenating the product name with the subproduct. This Dueling Excel podcast solves the problem with some VBA and with a formula.
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fun, this is our episode 134 - Merge Products and Subproducts!
Alright, today's question sent in from YouTube, wants to know if, from this table of products and subproducts if we can, first to get the product name with nothing, and then product name with additional text 1-6.
So kind of like this, we have this list of products, each product appears once without the subproducts, and then a list of all the subproducts and go again.
Alright so, I toyed with a formula that would do this, but as I started to think about the formula, I said "You know, VBA is the way to go." So, Alt+F11, Insert, Module and I create a little Sub here called Unwind().
Now, one thing I did is, wherever the active cell is when we start, that's going to be the first product.
You have to Select the first product, and remember that row, we're going to start to unwind things into that row.
You see, as I go, I keep adding one NextRow = NextRow + 1, that's how I know where to write the next value because, you know, obviously they're not going to line up, we're going to 7 rows for every row in the original table, so I loop.
For i = ActiveCell.Row To FinalRow . How do I figure out the final row?
Well I press Ctrl+Down arrow from the active cell.
And so for every row, first thing we do, go to the next row column, actually column 9, and the value there is the cell from row i,1 column A, and we don't concatenate anything after it.
Then go from column 2-7, and for each one we're putting the value from column 1, that's column A, a hyphen in quotes, and then value from column J, that'll be column 2-7.
Alright, so here's our Excel spreadsheet, come back here, press the Run button, wow, tht is just that fast, problem solved, there we go.
Alright Mike, what do you have?
Mike: Hey, thanks MrExcel!
You know, VBA wins this one, there's just no way around it, type that out.
The formula, I'm not even sure if it's a very efficient formula, but you know I will take the VBA over this any day.
Now, any time you try to unwind a data set, we want to, with formulas, we're going to look for patterns.
Now here's the pattern, we have Geo Fan and then this Kin-Light, it looks like we have to repeat it 7 times.
So if we're looking it up, we're going to need the number in our index to look this up, it's going to look up through this range.
We're going to need relative position 1-1-1-1-1-1-1 7 times, and then 2-2-2-2-2.
So we're going to have to somehow, in a formula, generate that pattern of numbers.
And then for the subproducts we're going to need, we'll use INDEX, and that's a two-way lookup.
We're going to need the row numbers, well we'll already get the row numbers from generating the 1s, and then the two relative positions from that first.
But then we're going to need the column number, which will be 1-2-3-4-5-6, so we're going to need to repeat 123456, 123456.
So, I'm actually going to hide these columns here, right click, Hide, so I have some room here.
The inside of most number incrementing formulas starts with the ROWS.
I'm actually going to build it from the point of view of I4, so I'm going to say I$4:I4 . The 4 is locked in the first reference, but not the second one, that'll be expandable range.
So I copy this down, that just gives me 1-2-3...14!
I'm going to have it all highlighted, so nominate(?) F2.
We're going to have to start with 0 for a bunch of our formulas, so I'm going to -1, this will give me 0...2...13.
Now for the 1-1-1-1-1 repeating, if we divide all of this by 7, let's see what this gives us.
So in parentheses I’m going to divide this by 7, Ctrl+Enter.
Well now we have an interesting pattern, if we were to use the INT function, INT gives us the integer part always going down, so these will all be 0, and these will all be 1.
So I'm going to F2 and put INT around this, the INT for integer, Ctrl+Enter, Esc, Esc, I forgot a parenthesis there.
Right so now, that's not quite what we want, we want 1's and then 2's, so we add 1 back in.
Oops, I hit the F1 key.
So I'm going to hit F2, and +1.
Alright, so that'll work for that first part.
Now, we're going to use this same ROWS-1, but now, instead of using the INT, we're going to put it inside the MOD.
Now the MOD gives us the remainder, so this will give us 0-13, but if we divide it by 7, that will give us the remainder, 0/7 the remainder is 0.
1/7 the remainder is 1, so we get a pattern always like 0-6, 0-6.
Now this little bit right here, we're actually going to use in our IF logical test, so we'll leave that there as one of the little formula bits.
This is not quite what we want because we want 1-7, so we simply add 1 back in.
So now, oh, this is going to get nasty, I'm sure there's a better way to do this.
Well, the first part, let's go ahead and copy these, I'm going to copy this, Ctrl+C, C to open up the clipboard, clear all.
I'll Ctrl+C, and then I'll Ctrl+C here, that's the +1, and I'll Ctrl+C, this will be for one of the logical test we run.
Zero will be interpreted as FALSE, all of the non-zero numbers would be interpreted as TRUE.
Alright, so let's do our first bit, that's the INDEX.
Now I'm going to look up everything under the Products, F4 comma, and the row number will be that 1-1-1-1-1-2-2-2-2-2 etc.
So Ctrl+Enter, and I can copy this down.
Actually, let's see, I'm going to Ctrl+C, and I don't know where the last cell is, so I'm going to scroll down, hold Shift and click, and then Ctrl+V.
So I've copied that all the way down, now I can just double-click.
Alright, so the next bit is, we're going to have to join and do our two-way look up.
So I'm going to go ahead and actually insert another column, and do that in a separate =INDEX, and we're going to do the whole two-way- notice the columns are hidden, but there still are part of our formula.
Ctrl+Shift+Down arrow, F4, and now I need a row number.
Well, the row number will be that little bit, comma, and the column number will be that little bit, that’s 1-2-3-4-5-6-7-1-2-3-4-5-6-7.
Alright, Ctrl+Enter and double click and send it down.
See, we get a reference error there, and F2, you know what?
We don't have 7 columns, we have 6 columns!
So for this column I'm going to get rid of the +1, Ctrl+Enter.
Now, that value doesn't matter, that's generated because we are, in essence, using this little bit right here, but that's the place where we're not going to use the second part, so that'll be OK.
Now I'm going to take this, copy, come to the end here, and I'm going to join it, and I'm going to say IF, and I want this little bit right here, this is going to give me 0-1-2-3-4-5-6-0!
So, the 0 will be the FALSE, the value IF FALSE, and that's when we're going to put nothing.
But the value IF TRUE is going to be, well we need a dash, right?
So I'm going in " - "& , and join it to- whoops!
I'll use my clipboard over here, I'm going to join it to that, please get the subproduct!
Alright now, here's the screen tip, and I'm already confused right here, these screen tips come to the rescue.
I'm going to type a comma, and then the value of FALSE is "". So up here, when it finds the 0, it'll put nothing, and we get just the product, alright, ) . I'm crossing my fingers, Ctrl+Enter...
This is just insane, double click and send it down, and it looks like that is working.
Insert this to keep it clean, and it looks like it is working.
Now you can see why we're giving the point to MrExcel for VBA!
Alright, we'll see you next trick!
Hey, alright Mike, you know, when I started to do the formulas the other day, I figure is going to be divided by 7 and then MOD over here, just wasn't coming together for me, that's why I switched over to VBA.
But that's good, I understand it now that you put it all together, so.
Good, good method there, especially if you don't want to use VBA.
I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and excelisfun!
I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fun, this is our episode 134 - Merge Products and Subproducts!
Alright, today's question sent in from YouTube, wants to know if, from this table of products and subproducts if we can, first to get the product name with nothing, and then product name with additional text 1-6.
So kind of like this, we have this list of products, each product appears once without the subproducts, and then a list of all the subproducts and go again.
Alright so, I toyed with a formula that would do this, but as I started to think about the formula, I said "You know, VBA is the way to go." So, Alt+F11, Insert, Module and I create a little Sub here called Unwind().
Now, one thing I did is, wherever the active cell is when we start, that's going to be the first product.
You have to Select the first product, and remember that row, we're going to start to unwind things into that row.
You see, as I go, I keep adding one NextRow = NextRow + 1, that's how I know where to write the next value because, you know, obviously they're not going to line up, we're going to 7 rows for every row in the original table, so I loop.
For i = ActiveCell.Row To FinalRow . How do I figure out the final row?
Well I press Ctrl+Down arrow from the active cell.
And so for every row, first thing we do, go to the next row column, actually column 9, and the value there is the cell from row i,1 column A, and we don't concatenate anything after it.
Then go from column 2-7, and for each one we're putting the value from column 1, that's column A, a hyphen in quotes, and then value from column J, that'll be column 2-7.
Alright, so here's our Excel spreadsheet, come back here, press the Run button, wow, tht is just that fast, problem solved, there we go.
Alright Mike, what do you have?
Mike: Hey, thanks MrExcel!
You know, VBA wins this one, there's just no way around it, type that out.
The formula, I'm not even sure if it's a very efficient formula, but you know I will take the VBA over this any day.
Now, any time you try to unwind a data set, we want to, with formulas, we're going to look for patterns.
Now here's the pattern, we have Geo Fan and then this Kin-Light, it looks like we have to repeat it 7 times.
So if we're looking it up, we're going to need the number in our index to look this up, it's going to look up through this range.
We're going to need relative position 1-1-1-1-1-1-1 7 times, and then 2-2-2-2-2.
So we're going to have to somehow, in a formula, generate that pattern of numbers.
And then for the subproducts we're going to need, we'll use INDEX, and that's a two-way lookup.
We're going to need the row numbers, well we'll already get the row numbers from generating the 1s, and then the two relative positions from that first.
But then we're going to need the column number, which will be 1-2-3-4-5-6, so we're going to need to repeat 123456, 123456.
So, I'm actually going to hide these columns here, right click, Hide, so I have some room here.
The inside of most number incrementing formulas starts with the ROWS.
I'm actually going to build it from the point of view of I4, so I'm going to say I$4:I4 . The 4 is locked in the first reference, but not the second one, that'll be expandable range.
So I copy this down, that just gives me 1-2-3...14!
I'm going to have it all highlighted, so nominate(?) F2.
We're going to have to start with 0 for a bunch of our formulas, so I'm going to -1, this will give me 0...2...13.
Now for the 1-1-1-1-1 repeating, if we divide all of this by 7, let's see what this gives us.
So in parentheses I’m going to divide this by 7, Ctrl+Enter.
Well now we have an interesting pattern, if we were to use the INT function, INT gives us the integer part always going down, so these will all be 0, and these will all be 1.
So I'm going to F2 and put INT around this, the INT for integer, Ctrl+Enter, Esc, Esc, I forgot a parenthesis there.
Right so now, that's not quite what we want, we want 1's and then 2's, so we add 1 back in.
Oops, I hit the F1 key.
So I'm going to hit F2, and +1.
Alright, so that'll work for that first part.
Now, we're going to use this same ROWS-1, but now, instead of using the INT, we're going to put it inside the MOD.
Now the MOD gives us the remainder, so this will give us 0-13, but if we divide it by 7, that will give us the remainder, 0/7 the remainder is 0.
1/7 the remainder is 1, so we get a pattern always like 0-6, 0-6.
Now this little bit right here, we're actually going to use in our IF logical test, so we'll leave that there as one of the little formula bits.
This is not quite what we want because we want 1-7, so we simply add 1 back in.
So now, oh, this is going to get nasty, I'm sure there's a better way to do this.
Well, the first part, let's go ahead and copy these, I'm going to copy this, Ctrl+C, C to open up the clipboard, clear all.
I'll Ctrl+C, and then I'll Ctrl+C here, that's the +1, and I'll Ctrl+C, this will be for one of the logical test we run.
Zero will be interpreted as FALSE, all of the non-zero numbers would be interpreted as TRUE.
Alright, so let's do our first bit, that's the INDEX.
Now I'm going to look up everything under the Products, F4 comma, and the row number will be that 1-1-1-1-1-2-2-2-2-2 etc.
So Ctrl+Enter, and I can copy this down.
Actually, let's see, I'm going to Ctrl+C, and I don't know where the last cell is, so I'm going to scroll down, hold Shift and click, and then Ctrl+V.
So I've copied that all the way down, now I can just double-click.
Alright, so the next bit is, we're going to have to join and do our two-way look up.
So I'm going to go ahead and actually insert another column, and do that in a separate =INDEX, and we're going to do the whole two-way- notice the columns are hidden, but there still are part of our formula.
Ctrl+Shift+Down arrow, F4, and now I need a row number.
Well, the row number will be that little bit, comma, and the column number will be that little bit, that’s 1-2-3-4-5-6-7-1-2-3-4-5-6-7.
Alright, Ctrl+Enter and double click and send it down.
See, we get a reference error there, and F2, you know what?
We don't have 7 columns, we have 6 columns!
So for this column I'm going to get rid of the +1, Ctrl+Enter.
Now, that value doesn't matter, that's generated because we are, in essence, using this little bit right here, but that's the place where we're not going to use the second part, so that'll be OK.
Now I'm going to take this, copy, come to the end here, and I'm going to join it, and I'm going to say IF, and I want this little bit right here, this is going to give me 0-1-2-3-4-5-6-0!
So, the 0 will be the FALSE, the value IF FALSE, and that's when we're going to put nothing.
But the value IF TRUE is going to be, well we need a dash, right?
So I'm going in " - "& , and join it to- whoops!
I'll use my clipboard over here, I'm going to join it to that, please get the subproduct!
Alright now, here's the screen tip, and I'm already confused right here, these screen tips come to the rescue.
I'm going to type a comma, and then the value of FALSE is "". So up here, when it finds the 0, it'll put nothing, and we get just the product, alright, ) . I'm crossing my fingers, Ctrl+Enter...
This is just insane, double click and send it down, and it looks like that is working.
Insert this to keep it clean, and it looks like it is working.
Now you can see why we're giving the point to MrExcel for VBA!
Alright, we'll see you next trick!
Hey, alright Mike, you know, when I started to do the formulas the other day, I figure is going to be divided by 7 and then MOD over here, just wasn't coming together for me, that's why I switched over to VBA.
But that's good, I understand it now that you put it all together, so.
Good, good method there, especially if you don't want to use VBA.
I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and excelisfun!
