Formula to push digits to the right.

[serge]

Hi,
I'm looking for a formula that will push to the right the digits as they come down from column B, so that the newest digits is on the left and the oldest is on the right.
This will show me how long a digits hasn't been selected, how many time it skip.

Thank you.

 

[duggie33]

I found a roundabout way of doing it in 365, but it would not work in 2007. At first, I thought something like "=TRANSPOSE($B$3:$B3)", copied down but it puts them in the wrong order I think. The second row of data would return 1 and 3 rather than 3 and 1.

Doug

 

[serge]

Than you dugggie33, but yes the order is what I'm looking for.

 

[Scott Huish]

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	1		1
4	3		3	1
5	5		5	3	1
6	8		8	5	3	1
7	2		2	8	5	3	1
8	6		6	2	8	5	3	1
9	7		7	6	2	8	5	3	1
10	0		0	7	6	2	8	5	3	1
11	9		9	0	7	6	2	8	5	3	1
12	4		4	9	0	7	6	2	8	5	3	1
13	5		5	4	9	0	7	6	2	8	5	3	1
14	9		9	5	4	9	0	7	6	2	8	5	3	1
15	0		0	9	5	4	9	0	7	6	2	8	5	3	1
16	4		4	0	9	5	4	9	0	7	6	2	8	5	3	1
17
Sheet1

Cell Formulas
Range	Formula
D3,D17,D16:Q16,D15:P15,D14:O14,D13:N13,D12:M12,D11:L11,D10:K10,D9:J9,D8:I8,D7:H7,D6:G6,D5:F5,D4:E4	D3	=IF(B3="","",TRANSPOSE(INDEX($B$3:B3,MAX(ROW($B$3:B3))-ROW($B$3:B3)+1)))

 

[Rick Rothstein]

NOTE: I realized after I posted the below reply that you do not have XL365. Sorry, but you will not be able to use this.

Since your values are all single digits, I was able to develop this single formula that will spill (generate) the entire triangular output for any size vertical range specified in the first variable (named 'b') inside the LET function.

=LET(b,B3:B16,c,COUNT(b),s,SEQUENCE(c),IFERROR(TEXTSPLIT(TEXTJOIN("/",,TRIM(RIGHT(CONCAT(MID(CONCAT(" "&b),2*(c-s+1),1)&" "),2*s)))," ","/"),""))

 

[Phuoc]

Or try this

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	1		1
4	3		3	1
5	5		5	3	1
6	8		8	5	3	1
7	2		2	8	5	3	1
8	6		6	2	8	5	3	1
9	7		7	6	2	8	5	3	1
10	0		0	7	6	2	8	5	3	1
11	9		9	0	7	6	2	8	5	3	1
12	4		4	9	0	7	6	2	8	5	3	1
13	5		5	4	9	0	7	6	2	8	5	3	1
14	9		9	5	4	9	0	7	6	2	8	5	3	1
15	0		0	9	5	4	9	0	7	6	2	8	5	3	1
16	4		4	0	9	5	4	9	0	7	6	2	8	5	3	1
Sheet1

Cell Formulas
Range	Formula
D3:Q16	D3	=IF($B3="","",IF(ROWS($B$3:$B3)<COLUMNS($D3:D3),"",INDEX($B$3:$B3,ROWS($B$3:$B3)-COLUMNS($D3:D3)+1)))

 

[Rick Rothstein]

Here is a very simple solution. Put this formula in cell D3...

=B3

and copy it down to cell D16 or, alternately, you can dispense with this formula and simply copy/paste B3:B16 to D3:D16, your choice. Next, put this formula in cell E4...

=IF(D3="","",D3)

and copy it down to E16, and then copy E4:E16 across until all blanks appear.

 

[serge]

Thank you Scott Huish,
Sorry or the late response, but I can not use your formula because starting row 13 the digit 5 is draw but it still in the row at L13 and should be removed since it is draw in the first column and so on for the rest of the digits, but it's my fault I should had mention it in my description.

 

[serge]

Thank you Phuoc, but same with yours, my bad sorry.

 

[serge]

Thank you Rick, your solution work for me I appreciate your help.