PDA

View Full Version : Transforming dataset into a different layout (Formula if possible?)



ashleyuk1984
10-15-2018, 02:44 PM
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.

https://images2.imgbox.com/cf/a8/IYuD66Ek_o.png (http://imgbox.com/IYuD66Ek)

Please take a look and let me know.

I've also attached the workbook.

Thank you very much.

23033

werafa
10-15-2018, 04:32 PM
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
10-15-2018, 04:46 PM
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
10-15-2018, 05:19 PM
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
10-15-2018, 05:22 PM
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
10-15-2018, 05:34 PM
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