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.