Joe needs to transpose all of the zip codes for each county into a single row. This is a tricky task - sort of like Repivoting data that Power Query unpivoted. In episode 1953, see how to use two formulas, filter, and text to columns to solve the problem.
Transcript of the video:
The MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1953 - Transpose for Each Unique ZIP Code (Repivot?)!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Joe, and what a great question, Joe was trying to transpose this data. Normally that's just copy and paste Special Transpose, but that's not the kind of transpose that Joe's trying to do.
Every time that we come up with a new county, he wants to have the ZIP codes going across, kind of like this, there's the county, there's all the zip codes.
Then the next county, than all the ZIP codes.
So, that's not that's not a paste Special Transpose at all!
And you know, it's funny, this kind of data is what we would get when we would go to Power Query and use the Unpivot feature.
But I really need to, kind of, Repivot!
This would have made a great Dueling Excel podcast, because I can think of lots of different ways to do this.
I would like to see what Mike would come up with, but this is what I'm going to do.
I'm adding two new columns out there called Keep and List, and by the way Joe, if you're watching, for whatever reason column C and D in your data were set to Center, and they were set to Text, here I had to change it back to general, or my formulas wouldn't work.
Alright, so the list is going to be this, it's going to say =IF( , this county is equal to the previous county.
Then, I want to take the previous item, &", ")& , and this ZIP code.
Otherwise, if it's not the same, that means we're starting a brand new thing, I'm just going to start with a ZIP code like that.
Alright, I'm going to double click to copy that down.
Alright, you see what happens is, it builds a list, builds a list, builds a list, builds a list, until we get to the very last item, that's the complete List.
Now right here, in the Keep column, =IF , actually we can just simplify it, =this<>to the next thing.
Alright, and what that does is, that returns TRUE, on the occasions where it's the last record for the county.
Now it looks like the data here is sorted by county, that's one of the assumptions, alright.
So once we have that, we're going to turn on the filters, Data Filter.
I'm going to go to Keep, and say that I want only the TRUE values, which gives me just the records I need.
From here, it's easy, I'm going to click on column A, CTRL+click on column D, Copy, and since this is a non-adjacent section, it will automatically paste it as values.
So I can just go to a new worksheet, Ctrl+V to paste, alright, and there is our list.
I'm actually going to do one more step here, where I just select all of those text to columns delimited by a comma, click Finish, and we're done!
Alright, so kind of unpivoting transposing for each county, convoluted set of steps, but should get the job done!
OK, I want to thank Joe for sending that question in, I want to thank for you stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1953 - Transpose for Each Unique ZIP Code (Repivot?)!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Joe, and what a great question, Joe was trying to transpose this data. Normally that's just copy and paste Special Transpose, but that's not the kind of transpose that Joe's trying to do.
Every time that we come up with a new county, he wants to have the ZIP codes going across, kind of like this, there's the county, there's all the zip codes.
Then the next county, than all the ZIP codes.
So, that's not that's not a paste Special Transpose at all!
And you know, it's funny, this kind of data is what we would get when we would go to Power Query and use the Unpivot feature.
But I really need to, kind of, Repivot!
This would have made a great Dueling Excel podcast, because I can think of lots of different ways to do this.
I would like to see what Mike would come up with, but this is what I'm going to do.
I'm adding two new columns out there called Keep and List, and by the way Joe, if you're watching, for whatever reason column C and D in your data were set to Center, and they were set to Text, here I had to change it back to general, or my formulas wouldn't work.
Alright, so the list is going to be this, it's going to say =IF( , this county is equal to the previous county.
Then, I want to take the previous item, &", ")& , and this ZIP code.
Otherwise, if it's not the same, that means we're starting a brand new thing, I'm just going to start with a ZIP code like that.
Alright, I'm going to double click to copy that down.
Alright, you see what happens is, it builds a list, builds a list, builds a list, builds a list, until we get to the very last item, that's the complete List.
Now right here, in the Keep column, =IF , actually we can just simplify it, =this<>to the next thing.
Alright, and what that does is, that returns TRUE, on the occasions where it's the last record for the county.
Now it looks like the data here is sorted by county, that's one of the assumptions, alright.
So once we have that, we're going to turn on the filters, Data Filter.
I'm going to go to Keep, and say that I want only the TRUE values, which gives me just the records I need.
From here, it's easy, I'm going to click on column A, CTRL+click on column D, Copy, and since this is a non-adjacent section, it will automatically paste it as values.
So I can just go to a new worksheet, Ctrl+V to paste, alright, and there is our list.
I'm actually going to do one more step here, where I just select all of those text to columns delimited by a comma, click Finish, and we're done!
Alright, so kind of unpivoting transposing for each county, convoluted set of steps, but should get the job done!
OK, I want to thank Joe for sending that question in, I want to thank for you stopping by, we'll see you next time for another netcast from MrExcel!
