Share this content

Reduce disparate collection of codes into database

Scrubbing & cleansing data

Didn't find your answer?

I download product data information from a car manufacturer’s portal into a spreadsheet.

The download produces two columns A & B, with headers Order Number (a customer car order) & Option Code (the options selected on that particular car order) respectively.

The data in col A (Order Number) is alpha numeric and all are unique, that is each order number only appears once in the column. There can be up to 100 orders.

The data in col B (Option Codes) is alpha numeric (of up to 6 digits). However, all the option codes (i.e. the option chosen for that car) are all displayed in column B, separated by a comma.

I can highlight column B and use “text to columns” to place each option code into each own column which can result in the spreadsheet data expanding to 60 further columns as some orders may have 60 options selected.

I wish to easily either or both:

a) Manipulate the options data so I have only two columns: Order Number and Option Code into strict database format with just records and fields. So, an order with 10 options would have ten rows each with the same order number in column A with the ten options listed one after the other in column B.

b) To be able to extract all the option codes displayed into one column of unique option codes. Bear in mind that a given option code may have been ordered (repeated) over several different customer orders. E.g. paint colour black.

If a non-macro solution could be found that would be preferred otherwise a foolproof macro would be still be appreciated.

I hope this makes sense. Thank you for any assistance with this.

Replies (7)

Please login or register to join the discussion.

By lannera
17th Jan 2022 15:05

I would suggest looking at PowerQuery in Excel. From my limited experience and reading round the subject, your data seems to need 'unpivoting' to generate 2 columns in a normalised form reading for a database.

Thanks (0)
By williams lester accountants
17th Jan 2022 16:16

Would you not be better asking this question on an Excel forum?

Thanks (0)
Replying to williams lester accountants:
By Hugo Fair
17th Jan 2022 16:34

Or a database forum (given that this would be simple to achieve with SQL)?

Thanks (0)
Replying to williams lester accountants:
By mung1
17th Jan 2022 18:59

I thought I had listed this under Excel - is it not then?

Thanks (0)
Replying to mung1:
By Hugo Fair
17th Jan 2022 19:31

It is, but only in the sense of being a 'topic type' within the 'Any Answers' section of an Accounting Forum.

So you may well strike lucky, but you could try a specialist forum as well maybe?

Thanks (1)
Routemaster image
By tom123
17th Jan 2022 16:38

I learned about pivot tables (many years ago) from this forum - and am just branching out to early steps in power pivot.

I think this is exact correct forum to ask on - :)

I watch with bated breath.

Thanks (0)
By paulwakefield1
17th Jan 2022 20:06

I agree with lannera's reply.

Either create the database using Text to Columns, load into PQ and unpivot the columns which will give you exactly what you want.

Or, my personal choice, load the original data straight into PQ and process it all in there. Although PQ has a programming language ("M") behind it, many operations (and all of the ones you would need) can be accessed from the ribbon. It is non destructive and easy to debug. What's not to like?* :-)

*Well there are a few things but very few.

PQ is well worth investing time in getting to grips with if you are involved in any processing of data (e.g. management accounts or linking disparate computing packages).

Thanks (1)
Share this content