Trick to transpose data based on specific field in Excel

Tips-&-Tricks • Mar 27, 2019

sometimes we stuck with issues like transposing data based on specific field in Excel.

Lets take a table for example as tagged below.

Table 1

Idea to get the data transformed in below form.

Horizontal representation of data

To get the desired result in Microsoft Excel you must be well aware of Excel functions and their nested use. Here we apply and nested formula to get the desired result.

=IF($A2="","",IF(COUNTA($D2:D2)<COUNTIFS($B$1:$B$23,$B2),INDIRECT(ADDRESS(MATCH($B2,$B$1:$B$23,0)+COLUMN()-5,3,1)),""))

Vertical to Horizontal transformation of data 

So apply above formula if you go stuck with such issues.

bye!

Best Excel training institute in Noida provides Basic Excel, Advance Excel and VBA offline and online classes.

If you want to learn Excel & VBA courses to be excel expert, connect us on:

ExcelHour
9599408486 / 8929231250
Website: https://www.excelhour.in/

Manoj kumar

Corporate Trainer at ExcelHour. I have devoted over 9 years to various industries for learning multiple data challenges and trained Excel and VBA courses to the corporate along with offline training.

No results for your search, please try with something else.

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Best Excel training institute in Noida provides Basic Excel, Advance Excel and VBA offline and online classes. If you want to learn Excel & VBA courses to be excel expert, connect us on: ExcelHour 9599408486 / 8929231250 Website: https://www.excelhour.in/