Sorting, Grouping, Transposing in Excel

[AJD10]

I know this can be done, but just not sure how to do it.

Have an excel sheet with names, address and order info with names appearing as many times as there are orders. Number of orders vary, anywhere from 1-20 or more.

Name1 Order1
Name1 Order2
Name1 Order3
Name2 Order1
Name3 Order1
Name3 Order2

This needs to be sorted and reformated

Name1 Order1 Order2 Order3
Name2 Order1
Name3 Order1 Order2

I tried to push it into Access, but it's the challenge of getting the orders into their own columns. Is there an Access routine for this?

 

[strongm]

Try investigating an Excel feature known as a PivotTable

 

[SkipVought]

Hi,

Can you explain the business case for generating this format?

How will this be used?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AJD10]

It's going to be used as input to create letters.. want one letter recipient with multiple orders in one record.

I've tried cross-tab queries in Access, but I'm having a mental block on this one.

 

[SkipVought]

Not a sound approch.

I'd do the entire thing in Excel, using the AutoFilter to select one addressee at a time. This can be accomplshed with some simple VBA code, so if you post your question in Forum707, you'll get some help putting this together.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2Grandpa]

Name order Name Order
Name1 Order1 Name1 Order1
Name1 Order2 Name1 Order2
Name1 Order3 Name1 Order3
Name2 Order1 Name2 Order1
Name3 Order1 Name3 Order1
Name3 Order2 Name3 Order2

Try using the Left() and Right() Excel Functions to seperate your data on your spreadsheet. I used the two functions examples below to seperate your test data based on finding a space, " ".
Name =LEFT(A2,LEN(A2)-FIND(" ",A2,1))
Order# =RIGHT(A2,LEN(A2)-FIND(" ",A2,1))
If your Order numbers have a stantard length such as "6" the function would be =Right(A2,6)and the Name function would be =LEFT(A2,LEN(A2)-7). Use 7 to remove the " " between the name and order.
Once you have done this you can import the data into Access and create a report for your letter.

 

[dham63]

Do you actually need any of the order information or do you just need to limit one line to a customer? If you are just looking to do that, it is easy enough to do using a pivot table and you will get info like this, with one customer per line.

order1 order2 order 3
Name1 1 1
Name2 1
Name3 1 1

 

[fneily]

I tried to push it into Access, but it's the challenge of getting the orders into their own columns. Is there an Access routine for this?"

You would not have Order1, Order2, etc. in table columns. This is against normalization which all Access table must follow.

Assuming that Name is split into two fields, as it's supposed to be, and Order is just some single field, then if you copied
Name1 Order1
Name1 Order2
Name1 Order3
Name2 Order1
Name3 Order1
Name3 Order2
into an Access table you can use:
faq701-3499

which is commonly used in the Access Forums.
You also might want to read:
thread68-1515213

and my discussion on how to normalize Excel spreadsheets which is the last post that looks like your order question.

 

[AJD10]

Thanks everyone for your comments!
I ended up doing the following:

- imported the table (as is) into Access.

- Created a routine to loop through the records and as long as Name/Address were the same, concatenate each field into one string, separated by a "*". (Name*Add*City*State*Order1*Order2)

- When name/address changed, then write out the string into a text file.

- Open up the file in XLS as delimited with a "*".

This worked out well and I can pass the MDB on to the user to do this everytime there's a similar file.