translating data in an oddly-constructed spreadsheet

[mzza]

Hi,

I have received some data in a very odd format and need to parse it into something more usable. Column A contains Unique IDs (Product Codes). Each Product will have one or more features, denoted by an Identifying Feature Code. However, the data is formatted so every potential Identifying Feature Code a Product might have is a column header in Columns B onwards. If a Product Code has a feature, this is denoted by a text description of the matching Identifying Feature Code in the column where that Identifying Feature Code occurs in Row 1. See this image as an example: https://ibb.co/dCoiET. Note that in the real data, the descriptive text does not contain the Identifying Feature Code.

To make this data more useful, I need to create a list of all Product Codes in a Column A of a new sheet, and list only those Feature Codes a product has in Column B (which will necessitate having duplicate Product Codes in Column A, as most Products have multiple features), viz: https://ibb.co/i2c4M8.

I'm drawing a blank on how to achieve this, I suspected some clever use of pivots might be the answer but I've not had any luck so far. Is there a formulaic solution? Or some combination of pivots and formulae? I'd prefer to avoid VBA if possible, but am open to any solution people have to offer!

TIA

MZZA

 

[ParamRay]

Hello. Do you have Power Query installed, or are you using Excel 2016 (in which 'Power Queries' are available under Data --> Get & Transform)?

If so, you can replace all your descriptive text values (from B2 onwards and downwards) with a 1, and then use the Unpivot Columns feature in Power Query...

 

[mzza]

I confess I didn't know such a thing existed, but having just played with it now, it still doesn't do what I need. I need duplicate values of the data in Column A (https://ibb.co/dCoiET) so that Column B can list each distinct code (from Row 1 in the same data) against each item in Column A (e.g.: https://ibb.co/i2c4M8)...

anyone else have any ideas? I think this might need to be VBA after all... :/

 

[ParamRay]

Hello again. I just achieved exactly what you're asking for using the steps in my last post...

 

[mzza]

Apologies ParamRay, user error is to blame (and not enough coffee). Just tried again with a fresh pair of eyes and it works as you said. Thank you so much for the assistance, you've saved me a ton of work!