Hi all,
My goal is to be able to take one format of sheet and, based on it, create another. Here's an example:
I have the following sheet which is a sheet I get from a source whose format I cannot control:
<tbody>
</tbody>
I need to create a new sheet which translates the values above into the values I actually need and then populate the sheet accordingly. I figure I could use a "conversion" sheet for this -- something like:
<tbody>
</tbody>
This translation sheet would populate a third "result" sheet like so
<tbody>
</tbody>
The idea here is that I could copy and paste columns and rows from Sheet 1, have them dynamically populate column A in sheet 2. Then I'd fill in column B in sheet 2. Next, the header row of sheet 3 is populated from the values I entered in column B, along with the matching values in the associated columns from Sheet 1.
I keep thinking this should be some kind of INDEX and MATCH thing and definitely not nested IFs. Maybe SUMPRODUCT?
Thanks for reading -- any help or guidance on how I can achieve this is greatly appreciated.
My goal is to be able to take one format of sheet and, based on it, create another. Here's an example:
I have the following sheet which is a sheet I get from a source whose format I cannot control:
A | B | C | D | E | F | G |
Foo | Bar | Ball | Baz | AAA | BBB | ... |
Value 1 | Value 2 | Value 3 | Value 4 | Value X | Value Y | ... |
<tbody>
</tbody>
I need to create a new sheet which translates the values above into the values I actually need and then populate the sheet accordingly. I figure I could use a "conversion" sheet for this -- something like:
A | B |
Foo | name |
Bar | description |
Ball | price |
Baz | color |
AAA | band_size |
BBB | band_material |
<tbody>
</tbody>
This translation sheet would populate a third "result" sheet like so
A | B | C | D | E | F |
name | description | price | color | band_size | band_material |
Value 1 | Value 2 | Value 3 | Value 4 | Value X | Value Y |
<tbody>
</tbody>
The idea here is that I could copy and paste columns and rows from Sheet 1, have them dynamically populate column A in sheet 2. Then I'd fill in column B in sheet 2. Next, the header row of sheet 3 is populated from the values I entered in column B, along with the matching values in the associated columns from Sheet 1.
I keep thinking this should be some kind of INDEX and MATCH thing and definitely not nested IFs. Maybe SUMPRODUCT?
Thanks for reading -- any help or guidance on how I can achieve this is greatly appreciated.
Last edited: