Transforming dataset into a different layout (Formula if possible?)

[ashleyuk1984]

Hi,

Please see screenshot below.

This shows an example of my dataset, and the layout how I would like it to be transformed...

Ideally, I would like to accomplish this with the use of a formula... But I feel like maybe the only way to do it, is VBA ?

I should be able to do it via VBA, but since I'm not the one that will be using the data - and they're not very good with Excel - I'd like to rule out the possibility of doing it with VBA and try to accomplish this with a formula first.



Please take a look and let me know.

I've also attached the workbook.

Thank you very much.

Dataset Example.xlsx

[werafa]

Hi Ashley,

add a 'helper row' to the original dataset, and number the columns (1,2,3.....), and a helper column to the final table

then you can use hlookup to transform the table
Werafa

[ashleyuk1984]

Hi werafa,

I kind of see where you're going with that... But how do I ignore the blank cells??

Doing it the way that I think you're talking about... gave me this:

Reference	Column	Machines
Ref1	1	Mach1
Ref1	2
Ref1	3
Ref2	1	Mach1
Ref2	2	Mach2
Ref2	3
Ref3	1	Mach1
Ref3	2	Mach2
Ref3	3	Mach3
Ref4	1	Mach1
Ref4	2
Ref4	3
Ref5	1	Mach1
Ref5	2	Mach2
Ref5	3

Maybe I'm not doing it correctly? That would be correct if I could ignore the blanks (I don't want to use filters)

[werafa]

To do this in a formula will require that you 'fix' (gambling terminology) the helper row for the lookup reference.

You could possibly do this with a countA on column 3 (eg Counta($c$1:C5) for the value in a5

[werafa]

ok, I've put the logic 90 degrees out. can you apply the counta to the original table, or do you need to make the table you have, and then do a second vlookup using the logic above to eliminate the gaps?

[werafa]

One more thought:

it might be possibel to do this using a pivot table.
to try, set the input range as a dynamic named range (search for "excel dynamic named range") for more on this, then set the dnr as the input range to the pivot table
you will also need to set a worksheet recalculate event to update the pivot cache to make sure this syncs automatically

I suspect that you can most easily use the table you have just created as the input, and it should work automatically from there

Werafa