Look Up value based on "X"

[pratheesh1983]

Hello

I have a spreadsheet like below used by a sales distributor:

In this sheet, 1st Column shows items the distributor sells. The next columns denotes the retail or supermarket group who buys items from the distributor. This sheet is maintained on daily basis, and if any supermarket or the retail guy buys any items then the distributor put a "X" in the column of the specific buyer and next to the item.


[TABLE="width: 604"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Items [/TD]
[TD]MG Retail[/TD]
[TD]A1 Super Market[/TD]
[TD]Empire Store[/TD]
[TD]PTK Retail[/TD]
[/TR]
[TR]
[TD]Rice[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Vegetable [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tooth Paste[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Detergents[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Bed Sheets[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Floor Cleaning Liquid[/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Water Bottles[/TD]
[TD]X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Spoon - Steel[/TD]
[TD] [/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sun scream Lotion[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X

[/TD]
[/TR]
</tbody>[/TABLE]

Requirements:

At EOD, distributor needs to convert the data from the above sheet in the below mentioned format which specify the item each buyer has brought on that particular day, and listed down just below to buyers name. So here I'm looking for a formula which will populate the item name below to the buyer name as soon as "X" is recorded in the data captured sheet. The buyer name and items will be always constant. Is there any excel formula which can help me this, please advice?

Thanks in advance!

Format

MG Retail


Vegetable
Floor Cleaning Liquid
Water Bottels
Sunscream Lotion






A1 Super Market


Rice
Tooth Paste
Detergents
Bed Sheets
Spoon - Steel
Sunscream Lotion




Empire Store


Rice
Tooth Paste
Bed Sheets
Spoon - Steel
Sunscream Lotion




PTK Retail


Rice
Detergents
Floor Cleaning Liquid
Sunscream Lotion

 

[Special-K99]

How about this?

In F1
=IFERROR(INDEX($A$1:$E$10,AGGREGATE(15,6,ROW(B$1:B$10)/((B$1:B$10<>"")),ROWS(A$1:A1))-(1-1),IF(ROW()=1,COLUMN()-4,1)),"")
copy across and down as far as H10.

Your buyers are in columns rather than a single list, where only the selected items are listed.

If you want to paste the output to a different sheet / position remember to Paste Values rather than normal Copy/Paste.

 

[pratheesh1983]

Thanks K99 - Had a question - If I have to place this formula in A11 cell, then what changes I need to make here?

 

[Special-K99]

On the same sheet?
Try

=IFERROR(INDEX($A$1:$E$10,AGGREGATE(15,6,ROW(B$1:B$10)/((B$1:B$10<>"")),ROWS(A$1:A1))-(1-1),IF(ROW()=1,1,1)),"")