Get unique list convert data row to column

[trprasad78]

Hi experts,

I need small favor i have attached excel file.

sheet input and sheet output (expected out put )

I had highlighted column which need to fill automatically based on input sheet.
only where ever data is their.

Only using excel formulas
Please note dont use pivotable and vba

thank you in advance

 

[rahulshewale1]

Hi @trprasad78 ,

Using Power Query is Much Easier.

see if is it ok?

Regard
Rahul Shewale

 

[trprasad78]

Hi @trprasad78 ,

Using Power Query is Much Easier.

see if is it ok?

Regard
Rahul Shewale

Thank you Mr. rahul,

we need to refresh to update the data, but i need formula base solution.

when ever input change output need to change automatically.
Because this file going to use mostly Tab

 

[trprasad78]

Any help to get the same result through by formula ?

 

[AlanSidman]

With Power Query, any time the original document/file is updated, one only has to click on the refresh button for the output to be updated. Suggest you look into learning PQ if this is the type of issues you will be encountering. It is a most powerful spreadsheet manipulator that is easy to use with no need to learn coding.

 

[Peter Bartholomew]

I can get the same result with formulas, but then I use dynamic arrays, Charles Williams's UNPIVOT function, FILTER and helper ranges to allow the use of SUMIFS. Is it worth doing? No. I would recommend using the more powerful parts of Excel that are built for the job, including VBA if you are worried you might forget to update the calculation.