Dynamically populate columns based on rows

[Steve_K]

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:

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.

 

[oldbrewer]

it would help if your first table had name,description etc OR does the data really say foo

 

[oldbrewer]

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	...
A	B
AAA	band_size	x
Ball	price	x
Bar	description	x
Baz	color	x
BBB	band_material	x
Foo	name	x
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
name	description	price	color	band_size	band_material
Value 1	Value 2	Value 3	Value 4	Value X	Value Y
	formula translating foo into name is
	=VLOOKUP(A4,mytable2,2)
	a10 to b15 (marked x) is called mytable2

<colgroup><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Steve_K]

Thanks for the reply! I wanted to show that the values in the first sheet can be anything -- there's no pattern or connection. Does that affect your solution?

 

[Steve_K]

I'd like to ask for a bit more clarification as I'm not following as well as I know I should be.

For your VLOOKUP statement above, can you describe the relationship between my three proposed tables? Is the "A4" coming from the first table or the second? I'm assuming we're looking for A4 from table 1 and seeing if there's a match from table 2. If there is, are we then pulling the column data from table 1 and spitting it out in table 3?

Thanks again for the insight and help.

 

[Lizard Of Confusion]

you could use =TRANSPOSE i am not very good at explaining some things i think it is highlight where you want the change to happen type in =Transpose(cell : range) <=== data to switch i am not where i first read about it here is Mw offices instructions they are not as good as the ones i saw just dont know where i saw them

https://support.office.com/en-us/ar...and-rows-ed1215f5-59af-47e6-953b-0b513b094dc2

 

[Lizard Of Confusion]

i think you have to ctrl + shift + enter on this too

 

[Lizard Of Confusion]

to put into another sheet =TRANSPOSE('Sheet6'!B8:B18)
=TRANSPOSE('Sheet6'!C8:M8)

i think you have to ctrl + shift + enter on this too

 

[oldbrewer]

to get name i look up A4 (foo) in the second table bringing back the word in column 2, to get value 1 under name I just use =A5

it is not a transposition imho

 

[Steve_K]

Thank you both - I can see the merit in both solutions. The VLOOKUP works best in this case for me.