1

Here is my situation. I need to take a list of 38k customer accounts and sort the information to a specific template that I can import to our new website. However the way the data is presented makes that difficult...

Every address saved for 1 customer is a separate line. So if you have a different billing or shipping address it would be 2 rows. Sometimes 3 rows. I need to move those rows into a column (Address 1, Address 2, etc.) under 1 row. So if CustomerA has 3 saved address, I need to take those 3 rows and move them into 3 columns of 1 row. I hope that makes sense.

I have no idea if a formula, pivot table, or VBA(I know as much as Jon Snow in this area) would be the best route to take. I tried pivot tables, but it's not really working out. I was thinking Index/Match might be the best solution - I'd just have to duplicate the formula for each Address column, but it'd get the job done. However I don't know how I can accommodate for the multiple rows.

Below is an example of the data I received. Line 20 is the template I need to move the rows into. Column g_user id is unique per customer, but with multiple rows of addresses for a single customer that value will be duplicated in the spreadsheet. Same with customer_no.

http://www.filedropper.com/excelhelp

Or view this image

Please let me know if you need more information and I will try to explain further.

EDIT - From the spreadsheet I posted, I need to take rows 2 and 3 and move the address information into new columns in row 1. So since address is 2 columns, city is 1, state is 1, zip code is 1, and country is 1 this makes 12 total columns of data from those 2 rows.

8
  • I would like to combine selected rows into columns.
    – Bschacht
    Commented Aug 24, 2017 at 19:48
  • Just an fyi, there are not a lot of people that will download and open a file from the internet. It would be better if you put the data in the Original Post using edit. Commented Aug 24, 2017 at 20:14
  • Its a whole table. I don't know how to add all that...
    – Bschacht
    Commented Aug 24, 2017 at 20:32
  • @ScottCraner And it isn't particularly clear how to download the file after clicking on the link provided. Other file sharing services (eg OneDrive; DropBox) provide a link which take you directly to the file. Commented Aug 25, 2017 at 1:02
  • Have you tried to get your screenshot into an Excel worksheet? It's not easy. And the link to your file takes me to a page of advertising for File Dropper. I suggest you use Markdown Table Generator to create a sample table from your data. After you paste the results into your original post, select the entire table and then the Code Sample icon so it will properly display. Commented Aug 25, 2017 at 1:07

4 Answers 4

0
  1. Select a blank cell for placing the combined content, enter formula =CONCATENATE(TRANSPOSE(B2:B19)) into the Formula Bar, then you need to select TRANSPOSE(B2:B19) part of the formula, and then press F9 key. You can see the formula has been changed as below screenshots shown.

enter image description here

  1. Remove the curly brackets from the formula in the formula bar, and then press the Enter key.
3
  • @Ramhound: Are you the organizer of this community?
    – user764661
    Commented Aug 24, 2017 at 20:47
  • And Looks like @CharlieRB is your assistant.
    – user764661
    Commented Aug 24, 2017 at 20:52
  • This isn't quite what I am looking for. I understand the concept, but I can't use this method for 38k lines...
    – Bschacht
    Commented Aug 24, 2017 at 21:00
0

I like using VBA for a task like this. Assuming you have a unique user ID as you show in your sample table, you could do something like this:

Sub Addresses_To_Columns()

    Dim lastRow As Long
    Dim addressCount As Integer: addressCount = 0

    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    For i = lastRow To 3 Step -1
        If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
            addressCount = addressCount + 1
            Range(Cells(i - 1, 10), Cells(i - 1, (addressCount * 6) + 10)) = Range(Cells(i, 4), Cells(i, (addressCount * 6) + 4)).Value
            Rows(i).Delete
        Else
            addressCount = 0
        End If
    Next i

End Sub

This will turn this: enter image description here

into this: enter image description here

This code will need to be customized to fit your specific format. Also note that this was done quickly as a proof of concept and may be missing some common best practices. Please backup your data before you try this code because it does contain a delete command to get rid of duplicate rows.

1
  • Ok, this looks like it will solve my problem, but lets just say I know absolutely nothing about VBA? Where do I start? I will try to figure it out... thanks
    – Bschacht
    Commented Aug 25, 2017 at 12:59
0

Thank you all for the help and suggestions., but I solved this using another method.

I simply concatenated a '-1','-2','-3' onto the "unique" customer IDs. Then I did a VLOOKUP for the unique-1,-2,-3 values for each new column I needed to add.

Lets hope all this time and work was worth it for the reputation gained from this ordeal.

1
  • This may no longer be the best solution, but it is the solution I used to achieve my stated goal.
    – Bschacht
    Commented Aug 28, 2017 at 16:22
0

Very simple solution, if I've correctly understood your query. Make sure to backup spreadsheet first obviously.

1) Add required number of new columns in correct place. Lets say new column is N.

2) I'm assuming the customer id is in Column A, and that lines with a new address for the same customer have the same number in Column A in that row. And finally assuming that the first row for each customer has the most complete details apart from the address ie name phone etc (if not then use sort, by sorting first by customer number and then by name).

3) In cell N2 type this formula:

=if(and(a3=a2,a1<>a2),h3,"")

where H is the column with the addresses. If the next row has the same customer no. (a3=a2), AND this row is a new customer, ie different customer number to previous row (a1<>a2), THEN this will take the address from the next row and put it in the main row for that customer. Repeat for all the other parts of that address so that all the info gets moved. Ie same formula but in O2 instead of N2 and referencing i3 instead of h3, and so on.

4) Do a similar formula for the 3rd address. Ie assuming column X is the new column for the 3rd address, in X2 type:

=if(and(a4=a2,a1<>a2),h4,"")

Basically the same, only taking information from two rows down instead.

5) By now all the addresses should be in the "main" row for each customer. Now you need to copy all those addresses, and paste them into the same locations, using Paste special - values. This will get rid of the formulas and just have the addresses as data as though you'd typed it in.

6) Now use sort by customer name to get all the rows which have blank for the name (because they're just extra addresses) into one place. Then delete all those rows, after ensuring that all the data you needed is now in the correct row. Voila

1
  • I wish you had posted this sooner! This looks like it could have also worked for me with less work.
    – Bschacht
    Commented Aug 28, 2017 at 16:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .