0

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.

  • I would like to combine selected rows into columns. – Bschacht Aug 24 '17 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. – Scott Craner Aug 24 '17 at 20:14
  • Its a whole table. I don't know how to add all that... – Bschacht Aug 24 '17 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. – Ron Rosenfeld Aug 25 '17 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. – Ron Rosenfeld Aug 25 '17 at 1:07
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.
  • @Ramhound: Are you the organizer of this community? – user764661 Aug 24 '17 at 20:47
  • And Looks like @CharlieRB is your assistant. – user764661 Aug 24 '17 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 Aug 24 '17 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.

  • 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 Aug 25 '17 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.

  • This may no longer be the best solution, but it is the solution I used to achieve my stated goal. – Bschacht Aug 28 '17 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

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

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.