Excel formula

[SDTMN]

Hi all,

I couldnt find any help on my small issue, so please help if you can.

I have an excel which on "A" sheet has data on horizontal so A1=Me,B1=You,C1=They and then on the "B" sheet i retrieve these values and concatenate with some text but on vertical, so A1=Me,A2,You,A3=They. So far so good but then when i have values in the "A" sheet on A2,B2,C2 and so on , on the second sheet I want to just copy past the formulas from above but still to keep the pase by incrementing all 3 values with 1 not every value with 1. Does it make sense?


Thanks.

 

[FormR]

Hi, welcome to the forum!

Something like this maybe?

Excel 2013
	A
1	Me some text
2	You some text
3	They some text
4	Other 1 some text
5	Other 2 some text
6	Other 3 some text
7	More 1 some text
8	More 2 some text
9	More 3 some text
Sheet B

Cell Formulas
Range	Formula
A1	=INDEX('Sheet A'!$A$1:$C$1000,ROUNDUP(ROWS(A$1:A1)/3,0),MOD(ROWS(A$1:A1)-1,3)+1)&" some text"

Excel 2013
	A	B	C
1	Me	You	They
2	Other 1	Other 2	Other 3
3	More 1	More 2	More 3
Sheet A

 

[SDTMN]

Hi FormR, thanks for looking into this and apologies for my late reply, ive been in a mini holiday

Thats not what I want, let me try to explain, below are the columns, A,B,C,D and on X I need a foruma to keep the pase like on the X2 to show me the value of B2, then on X3 to put the value of C2 and on X3 the value of D2. Then when i drag down to know to go to the next line which is line 3 and put the next 3 values.

	A	B	C	D						X
		Code	Description	Name
1	Supplier1	Nike	Nike shoes	Nike Inc						Nike
2	Supplier2	Addidas	Addidas shoes	Addidas Co.						Nike shoes
3	Supplier3	Pepsi	Pepsi drink	Pepsi Ltd						Nike Inc
										Addidas
										Addidas shoes
										Addidas Co.

<colgroup><col span="3"><col><col><col span="5"><col></colgroup><tbody>
</tbody>

Hope the above makes sense...

 

[FormR]

Hi, like this? - formula in X2 can be copied down as required.

Excel 2013
	A	B	C	D	E	X
1		Code	Description	Name
2	Supplier1	Nike	Nike shoes	Nike Inc		Nike
3	Supplier2	Addidas	Addidas shoes	Addidas Co.		Nike shoes
4	Supplier3	Pepsi	Pepsi drink	Pepsi Ltd		Nike Inc
5						Addidas
6						Addidas shoes
7						Addidas Co.
8						Pepsi
9						Pepsi drink
10						Pepsi Ltd
Sheet1

Cell Formulas
Range	Formula
X2	=INDEX($B$2:$D$1000,ROUNDUP(ROWS(X$2:X2)/3,0),MOD(ROWS(X$2:X2)-1,3)+1)

 

[SDTMN]

Yes, Brilliant ! Can you help me understand it?

 

[FormR]

Can you help me understand it?

Hi, glad it helped.

It might help your understanding if we split the formula up a little like below:

Excel 2013
	A	B	C	D	E	F	G	H	I
1		Code	Description	Name		Row Formula is in	Row to index	Col to index	Get row & col from range
2	Supplier1	Nike	Nike shoes	Nike Inc		1	1	1	Nike
3	Supplier2	Addidas	Addidas shoes	Addidas Co.		2	1	2	Nike shoes
4	Supplier3	Pepsi	Pepsi drink	Pepsi Ltd		3	1	3	Nike Inc
5						4	2	1	Addidas
6						5	2	2	Addidas shoes
7						6	2	3	Addidas Co.
8						7	3	1	Pepsi
9						8	3	2	Pepsi drink
10						9	3	3	Pepsi Ltd
Sheet1

Cell Formulas
Range	Formula
F2	=ROWS(F$2:F2)
G2	=ROUNDUP(F2/3,0)
H2	=MOD(F2-1,3)+1
I2	=INDEX($B$2:$D$1000,G2,H2)

To gain a better understanding of the individual functions used the built in help is always a good place to start.

 

[SDTMN]

You are fantastic Sir!You make internet a better place Happy hollidays!