Excel Formula to create an organized Data Sheet from an unorganized one.

Question

Hi,&nbsp;I am practicing and I am just stuck with a problem if we have an unorganized list given to us(as it is given below).&nbsp;ABCDEFGHIJKLMNOPQRST1InvoiceProduct NameQuantityRateAmountInvoiceProduct NameQuantityRateAmountInvoiceProduct NameQuantityRateAmountInvoiceProduct NameQuantityRateAmount2C100BB525290152250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C101AD40020080000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C102GH700180126000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5C103HJ320120157892&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;So as you can see the Raw data is from (A1 - O5) and has multiple matching headers from (A1 - O1).Now I want to create a formula so that all this data organized inside (P2 - T5) based on there headers (P1 - T1) as below:&nbsp;&nbsp;ABCDEFGHIJKLMNOPQRST1InvoiceProduct NameQuantityRateAmountInvoiceProduct NameQuantityRateAmountInvoiceProduct NameQuantityRateAmountInvoiceProduct NameQuantityRateAmount2C100BB525290152250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C100BB5252901522503&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C101AD40020080000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C101AD400200800004&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C102GH700180126000C102GH7001801260005C103HJ320120157892&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C103HJ320120157892&nbsp;Please help me out thankyou.&nbsp;&nbsp;&nbsp;

dscheikey · Answer

Sourav91&nbsp;You can use the following formula in cell P2 and then copy it down:=INDEX(A2:O2,1,SEQUENCE(1,5,XMATCH(TRUE,A2:O2&lt;&gt;"")))&nbsp;

Forum Discussion

Excel Formula to create an organized Data Sheet from an unorganized one.

1 Reply

Resources