Display results horizontally

[ThrottleWorks]

I have two columns, A & B

Column A is ID, column B is Group ID


Cell a1 = abc12

Cell a2 = abc13

Cell a3 = abc14

Cell a4 = abc15


Cell b1 = 1

Cell b2 = 1

Cell b3 = 1

Cell b4 = 2


I want to write a formula in range (“C1:f1”).

Formula will be written in cell c1, reference of the formula is cell b1.

I want the result as abc12, this is the value in cell a1


Similarly the formula in cell d1 will give me result abc13


I want a formula which will display the results horizontally.

I am looking for a formula which will populate the values in column A bases on the group Id in column B.

For example value in the cell b1 can be assigned to multiple values in column A.

The formula will be in cell c1,d1,e1,f1 …. And the results will be from cell a1,a2,a3,a4


Can someone help me in this please.

 

[Faseeh]

Hi sachinbizboy,


You can use:

=OFFSET($A$1,COLUMN(A1)-1,0)

Drag to left, can't understand what do you mean to say with "reference of the formula is cell B1".


Regards,

 

[NARAYANK991]

Hi Sachin ,


I am not able to understand the significance of the values in column B on the results in the range C1:F1.


In A1 through A4 , you have the following values :

[pre]

abc12
abc13
abc14
abc15
Now , suppose , in the range B1:B4 , the values were :

1
2
1
2

[/pre]
What would the results in the range C1:F1 be ?


How would the values in column B change the results in the range C1:F1 ?


Narayan

 

[ThrottleWorks]

Sir extreamally sorry for the late reply.


I have following range a1,a2,a4,a5

value in the range 100,200,300,400,500


another range b1,b2,b3,b4,b5

value in the range 1,1,1,2,2


third range where i will write a formula

c1,d1,e1,f1,g1


reference in all the formula is cell b1

values for the range c1,d1,e1,f1,g1

100,200,300,,


cell f1 & g1 will be blanck in this case


sorry for confusing you.

 

[Faseeh]

Hi sachin,


Have u checked my solution, u can further wrap it in IFERROR() if u want blanks in case of error,


regards,

 

[Debraj]

Hi Sachin,


{=IFERROR(INDEX($A$1:$A$5, SMALL(IF($B$1:$B$5=$B$1,ROW($B$1:$B$5)), COLUMNS($B$1:B$1)),ROWS($B$1:B$1)), "")}

Confirm the Formula by Ctrl + Shift + Enter

for fixed B1 as reference..


You may have some reason to set Reference to B1 fixed, but I am still unable to figure it out.. anyway.. you can change $B$1 to $B1 if you need to drag it down also..


Regards,

Deb

 

[ThrottleWorks]

Extreamally sorry to everyone for such a delayed reply, I was on a leave, just came to ofice, will share the results, thanks a lot for the help, sorry once again.