spreadsheet data rearranging from columns to row, can i automate this?
April 28, 2019 2:47 AM Subscribe
I'm not a proficient excel user. I have a huge list of stores with their respective addresses, all in one column, like this.
I need to format the same data but in rows, all one under another,
like that.
Can i automate this somehow? I have hundreds of entries... I just spent 2 hours researching this and can't seem to formulate the correct search terms to explain what i need to do.
I have access to Excel, Numbers and google sheets if that helps.
Bonus points for a free solution that won't require a plugin or extension as i don't have admin rights on this laptop to install new stuff. If that's the only way i might be able to borrow another computer for a bit.
If the only way to do this is a paid programme or service, i might still be interested, it still could turn out cheaper than hours of cut and paste monkey work.
Thanks!
PS: advanced mode: some of the addresses have two rows for the street + some extra info instead of just one. There's always one empty row between each store details. If that can't be automated i can first manually remove those before i run whatever script or process needed.
Bonus points for a free solution that won't require a plugin or extension as i don't have admin rights on this laptop to install new stuff. If that's the only way i might be able to borrow another computer for a bit.
If the only way to do this is a paid programme or service, i might still be interested, it still could turn out cheaper than hours of cut and paste monkey work.
Thanks!
PS: advanced mode: some of the addresses have two rows for the street + some extra info instead of just one. There's always one empty row between each store details. If that can't be automated i can first manually remove those before i run whatever script or process needed.
Sadly, I don’t think you can use Guess What’s method because all your data is in a single column.
What I would do with your format isn’t quite automated, but easier than cutting and pasting one by one. This is based on your example sheet where the data starts on column b, row 2 and there’s a blank row in between.
Method 1
Select all the data.
Cut and paste into Word.
Under Edit, select Find and Replace.
In Find: ^13[!^13]
this should find a return followed by anything but another return
In Replace: ^t
this is a tab
Replace all (whole document)
Select all
Under Insert, select Convert Text to Table
You want to Separate text at tabs
You should end up with the table you want with an extra blank column.
Select the columns you want and copy paste back into a blank Excel spreadsheet
posted by Kriesa at 4:31 AM on April 28, 2019 [2 favorites]
What I would do with your format isn’t quite automated, but easier than cutting and pasting one by one. This is based on your example sheet where the data starts on column b, row 2 and there’s a blank row in between.
Method 1
Select all the data.
Cut and paste into Word.
Under Edit, select Find and Replace.
In Find: ^13[!^13]
this should find a return followed by anything but another return
In Replace: ^t
this is a tab
Replace all (whole document)
Select all
Under Insert, select Convert Text to Table
You want to Separate text at tabs
You should end up with the table you want with an extra blank column.
Select the columns you want and copy paste back into a blank Excel spreadsheet
posted by Kriesa at 4:31 AM on April 28, 2019 [2 favorites]
First, your desired results example is a Google spreadsheet that is private (if you set it to public then anyone can see it without having to request permission).
Second, I've gone ahead and mocked up a solution in a public Google spreadsheet that I am guessing is what you need, based on my reading of the original question. This method uses formulas that you can just drag down and also uses INDEX MATCH, which is something that I think any Excel user who has to do complicated data lookups should know how to use (it will make your life so much easier).
It assumes:
- there is always 1 empty row between stores
- there are always 3 rows of data for each store (i.e. I ignored your advanced mode, which makes it quite a bit more complicated to fix)
- the original data starts in row 2 with headers in row 1
Conceptually, to explain what I'm doing in the Google sheet:
- Column B - this assigns each store a unique number, so all rows for store 1 are "1", store 2 are "2", etc.
- Column C - this generates a looping 1/2/3 sequence. The idea here is that you need to consistently identify every "type" of data with a consistent number; so in my spreadsheet, the rows that have store names are always "1", the rows that have street names are always "2", and the rows with city names are always "3".
- Index Match formula, columns E to H: what I'm doing here is using an INDEX MATCH formula, which is a really powerful (like VLOOKUP on steroids) tool that I highly recommend any fellow Excel users like me to learn if you don't know it already. Basically, what's happening in the table is that it's using those two numbers we assigned in columns B and C to transpose the data in your original column to your desired format.
You just have to make sure that the range of the data in the formula covers all your original data. I purposefully did not do this -- you'll see that the formulas read rows 2 through 20, whereas the data actually goes through row 24, which is why the data for store #6 fails to be brought in correctly.
There are a number of details in the version of the formula I'm using (in particular the fact that's an array formula). I can try to pop back in with more details if you end up wanting to know more.
posted by andrewesque at 5:56 AM on April 28, 2019
Second, I've gone ahead and mocked up a solution in a public Google spreadsheet that I am guessing is what you need, based on my reading of the original question. This method uses formulas that you can just drag down and also uses INDEX MATCH, which is something that I think any Excel user who has to do complicated data lookups should know how to use (it will make your life so much easier).
It assumes:
- there is always 1 empty row between stores
- there are always 3 rows of data for each store (i.e. I ignored your advanced mode, which makes it quite a bit more complicated to fix)
- the original data starts in row 2 with headers in row 1
Conceptually, to explain what I'm doing in the Google sheet:
- Column B - this assigns each store a unique number, so all rows for store 1 are "1", store 2 are "2", etc.
- Column C - this generates a looping 1/2/3 sequence. The idea here is that you need to consistently identify every "type" of data with a consistent number; so in my spreadsheet, the rows that have store names are always "1", the rows that have street names are always "2", and the rows with city names are always "3".
- Index Match formula, columns E to H: what I'm doing here is using an INDEX MATCH formula, which is a really powerful (like VLOOKUP on steroids) tool that I highly recommend any fellow Excel users like me to learn if you don't know it already. Basically, what's happening in the table is that it's using those two numbers we assigned in columns B and C to transpose the data in your original column to your desired format.
You just have to make sure that the range of the data in the formula covers all your original data. I purposefully did not do this -- you'll see that the formulas read rows 2 through 20, whereas the data actually goes through row 24, which is why the data for store #6 fails to be brought in correctly.
There are a number of details in the version of the formula I'm using (in particular the fact that's an array formula). I can try to pop back in with more details if you end up wanting to know more.
posted by andrewesque at 5:56 AM on April 28, 2019
In cell C2 put this formula: =IF(MOD(ROW(B2)-2,4)=0,B2,"")
In cell D2 put this formula: =IF(MOD(ROW(B2)-2,4)=0,B3,"")
In cell E2 put this formula: =IF(MOD(ROW(B2)-2,4)=0,B4,"")
Then select cells C2, D2 & E2 & drag the corner down to copy them to the end of the list
Next copy the whole list & Paste As Values (do a right click & hover the mouse over the Paste options until you find this one) into a new sheet.
Then select the whole list & sort it, to group all the rows with text together.
posted by cantthinkofagoodname at 6:03 AM on April 28, 2019 [1 favorite]
In cell D2 put this formula: =IF(MOD(ROW(B2)-2,4)=0,B3,"")
In cell E2 put this formula: =IF(MOD(ROW(B2)-2,4)=0,B4,"")
Then select cells C2, D2 & E2 & drag the corner down to copy them to the end of the list
Next copy the whole list & Paste As Values (do a right click & hover the mouse over the Paste options until you find this one) into a new sheet.
Then select the whole list & sort it, to group all the rows with text together.
posted by cantthinkofagoodname at 6:03 AM on April 28, 2019 [1 favorite]
Best answer: Yet another way. This way really REALLY assumes that you never ever ever have a fourth row of data for any store and never ever skip more than one row.
After you were done, you'd probably want to crtl-a ctrl-c to copy the entire sheet, then alt-e s v to paste into itself as values instead of formulas, and then delete column a and save as a new spreadsheet.
posted by GCU Sweet and Full of Grace at 6:21 AM on April 28, 2019
After you were done, you'd probably want to crtl-a ctrl-c to copy the entire sheet, then alt-e s v to paste into itself as values instead of formulas, and then delete column a and save as a new spreadsheet.
posted by GCU Sweet and Full of Grace at 6:21 AM on April 28, 2019
Yes! You can do this by recording a macro. You need to add the developer toolbar, which you should be able to do without permissions.
Insert at least 4 rows at the top.
Title them "Store Name", "Street Name", "City".
Start "Record Macro".
It will ask for a shortcut - I use "Ctrl+R".
Select the 4 cells: "Store Name 1", "Street Name 1", "City 1", and the empty cell below them.
Copy the 4 cells and paste into cells B1 through B4.
Right click the original 4 cells, right click, and select "delete cells". When it gives you the option, select the one that moves the data up.
Right click column B and select "insert row."
Stop recording.
Now, run the next step with your shortcut (Ctrl+R). This will move the next set of store info up. Make sure you save your workbook at this point, once you've verified the macro is working. Then keep running the shortcut until all your data is shifted over.
At this point, you should have 3 rows with all the data. I couldn't open your 2nd link, so I'm not sure if you want the data in rows or columns. If you want it in columns, select all of your data, copy it, and select the "transpose" paste option - this will switch it into columns.
posted by DoubleLune at 6:22 AM on April 28, 2019 [1 favorite]
Insert at least 4 rows at the top.
Title them "Store Name", "Street Name", "City".
Start "Record Macro".
It will ask for a shortcut - I use "Ctrl+R".
Select the 4 cells: "Store Name 1", "Street Name 1", "City 1", and the empty cell below them.
Copy the 4 cells and paste into cells B1 through B4.
Right click the original 4 cells, right click, and select "delete cells". When it gives you the option, select the one that moves the data up.
Right click column B and select "insert row."
Stop recording.
Now, run the next step with your shortcut (Ctrl+R). This will move the next set of store info up. Make sure you save your workbook at this point, once you've verified the macro is working. Then keep running the shortcut until all your data is shifted over.
At this point, you should have 3 rows with all the data. I couldn't open your 2nd link, so I'm not sure if you want the data in rows or columns. If you want it in columns, select all of your data, copy it, and select the "transpose" paste option - this will switch it into columns.
posted by DoubleLune at 6:22 AM on April 28, 2019 [1 favorite]
Response by poster: Thanks everyone! Sorry i didn't set the correct sharing settings on the second file. It's fixed now.
I ended up using GCU Sweet and Full of Grace' version and just manually edited the few entries that had 4 rows. This saved me HOURS of work, i'm super grateful! I hope it comes in handy for someone else in the future.
posted by PardonMyFrench at 10:58 AM on April 28, 2019
I ended up using GCU Sweet and Full of Grace' version and just manually edited the few entries that had 4 rows. This saved me HOURS of work, i'm super grateful! I hope it comes in handy for someone else in the future.
posted by PardonMyFrench at 10:58 AM on April 28, 2019
« Older Sex positions and significant height difference... | What should I do with the space above my washer... Newer »
This thread is closed to new comments.
posted by Guess What at 3:09 AM on April 28, 2019