Converting itemized list to single row entries with multiple datatypes

[ShamanWolf]

Not sure which forum this one should be under within excel.

I'm trying to create an automated method for creating billing packets from a list of shipments, and I've tried a few things, against the before version of the list, so far: sumifs, vlookup, etc... but always end up having to make manual edits to force it to display data correctly; (removing duplicate dates, converting alpha data to numeric, etc..) due to the structure of the itemized shipment list. (which comes from a report that I cannot change the formatting of.)

My biggest hurdle is that one shipment can have a varying number of stores (up to 3), orders across three order-types, and redundant data across all rows

The only option I see, is to come up with a way to condense the itemized shipment list into single row entries broken down by information per store delivery(stop 2, stop 3, stop 4). Just not sure how to go about it (formula vs macro), any advice would be greatly appreciated.

I put together a mockup example of what I'm starting with [Before] and desired result [After]in the attachment.

Again thanks for any help with this,

[FlameRetired]

Hello ShamanWolf. Welcome to the forum.

Before embarking can you clarify some things?

• In the sample 'After' the Stop# are listed in descending order where there are multiples. Is sort order required?
• The unique shipment orders can be easily returned by simply using the Remove Duplicates feature. Is that acceptable or do you prefer formula for that as well?

[GeoffW283]

Attached in sheet "GW" is my attempt to match the desired results on the "After" worksheet.

Relative to Dave's questions I have made no attempt to sort by Stop # and I have used a formula to derive the list of unique shipment orders.

I introduced a new row-2 with Stop numbers so that formulas can be copied across each of the three stops. Nevertheless there is something of a mish mash of formulas for the various columns.

I have shaded cells green on the "GW" worksheet where they agree with the desired results on the "After" worksheet and orange where they do not agree. All of the disagreements are with DO numbers and the XD/CP columns. As best as I can tell my results are what would be expected from the data. Can you double check your expected answers in this area and if there is no mistake then please clarify the logic behind how there cells should be calculated.

Note that this is a macro workbook - macros must be enabled for this solution to work unless you have Excel 365. I use a UDF equivalent of the TextJoin() function (courtesy of Bo Ry).

[ShamanWolf]

FlameRetired,

Thanks for taking a look at this. The stop# column in 'Before' would determine which columns the data from that line would go to in 'After' column layout, some of it having to be summed, combined cells with comma or forward slash delimited

Stop 1 is always our facility
Stop 2 is the 1st delivery last thing loaded on all trailers.
Stop 3 is the 2nd delivery (nose on 2 delivery, middle on 3 delivery trailers.)
Stop 4 is the 3rd delivery on a 3 delivery trailer.

End result, I'm looking for something end user friendly; that I can teach my clerks to use. Most of whom are amateur to novice Excel users, and who are resistant to complex changes.

I'm just at an intermediate user myself, but am eager to learn, expand upon and build new and more efficient/lean tools for my colleagues I to work from.

[ShamanWolf]

Also we're operating on Office 2013

[FlameRetired]

Aha! I missed the Stop # column.

[ShamanWolf]

Geoff,

This solution works great, I knew there would be a lot of #N/A due to the fluid nature of the list. Greatly appreciated.

Thanks Again,
B

[GeoffW283]

No problem, glad to help and thanks for the feedback