Need Excel data Consolidation where some in Horizontal and some data in vertical.

[Bakiyaraja]

Hi all,

i Need you people help to consolidate data from Excel sheet

Excel data Consolidation where some data in Horizontal and some data in vertical.

i have attached input and required output after consolidation

Could you please help me resolve 

[wale_ilori]

I haven't had time to look at the data files you sent yet but typically Crosstab and Transpose tools will pivot your data from horizontal to vertical and vertical to horizontal as desired.

[Bakiyaraja]

Thank you @wale_ilori  Yes i am aware of those tools but there is complexity on the data if u have a look i hope u can able to understand and help me out

[wale_ilori]

I've had a quick look at the data. I see what you mean. Could possibly create a solution after working hours. A few rounds with the crosstab and transpose but it doesn't look too difficult.

[Bakiyaraja]

@wale_ilori  workflow would be really helpful could you please help me on that 

[wale_ilori]

Ok @Bakiyaraja Sorry I was really tied up so I have 2 slightly different methods to show you how you can get to the same output. The file is attached.

The first method splits the sheet into 2 sections and then uses a series of tools to shape the data individually and then merged together.

The second method takes a slightly different approach to extract the data by using 2 different ranges from the file so pay attention to the input file configurations to see how the data was extracted.

And if you really want to match the output with the color coding, then exporting to excel using the report tools to color the sections will do that for you too.

All the best.

Wale