Concatenate and Combine numbers.

[serge]

Hi everyone,

I'm asking for 2 formulas in this same post because it's linked together otherwise let me know and I will start a second tread for the second one, Thank ou all for any help.

The first formulas need to start in K3, across and down, that will combine C3:E7 with G3:I7 which should return the results in K3:P7 ( in yellow ).

The second formula need to combine the numbers in K3:P7 in sets of 5 numbers, which the return are in R3:V15 ( Blue )

 

[t0ny84]

Hi serge,

For the first part wouldn't =CONCATENATE

E.g. for K3
=CONCATENATE(C3,G3)
C3 = 5
G3 = 2
K3 would show 52.

E.g. for L3
=CONCATENATE(C3,H3)

Then copy down the rows.

For the second part wouldn't =CellNumber
In R3 - =$K$3

Or am I not understanding your question correctly?

 

[serge]

Hi t0ny84,

Thank you for answering my post, The CONCATENATE formula work only for the first column, if I drag it to the right , it need to return 52,54 then 62,64,72,74, it need to grab the first digit in C3 with the 2 other digits in G3:I3, then switch to the second digit in D3 with G3:I3 and so on.That will give me the result table in yellow.

For the second formula I'm looking for a formula that will use the numbers in K3:P7, that will permute them in sets of 5 numbers using those 5 rows. you can see the return table in R3:V14.
Thank you.

 

[Bo_Ry]

Please try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2
3			5	6	7		2	4			52	54	62	64	72	74		52	84	75	67	35
4			8				4				84							52	84	75	67	35
5			7				5				75							54	84	75	97	35
6			6	9			7				67	97						54	84	75	97	35
7			3				5				35							62	84	75	97	35
8																		62	84	75	97	35
9																		64	84	75	97	35
10																		64	84	75	97	35
11																		72	84	75	97	35
12																		72	84	75	97	35
13																		74	84	75	97	35
14																		74	84	75	97	35
Sheet1

Cell Formulas
Range	Formula
K3:P7	K3	=IF(COLUMNS($K3:K3)>COUNT($C3:$E3)*COUNT($G3:$I3),"",INDEX($C3:$E3,INT((COLUMNS($K3:K3)-1)/COUNT($G3:$I3))+1)*10+INDEX($G3:$I3,MOD(COLUMNS($K3:K3)-1,COUNT($G3:$I3))+1))
R3:V14	R3	=IF(INDEX($K$3:$P$7,COLUMNS($R3:R3),INT((ROWS(R$3:R3)+1)/2))="",R2,INDEX($K$3:$P$7,COLUMNS($R3:R3),INT((ROWS(R$3:R3)+1)/2)))

 

[mikerickson]

In K3, put =10*C3+G3 and drag right and down.

 

[serge]

Hi Bo-ry,
For the yellow table, I'm not getting the same result !!! what I'm doing wrong ?

For the second formula every sets are duplicated !

 

[serge]

In K3, put =10*C3+G3 and drag right and down.

Hi Mike,

Using your formula, I'm not getting the result I'm looking for, it's a bit different. This is what I get.

 

[jasonb75]

Hi Bo-ry,
For the yellow table, I'm not getting the same result !!! what I'm doing wrong ?

Your mistake was posting your example in a screen capture, if you used XL2BB instead then we would have been able to see exactly what is in the cells.

I came up with a formula that was almost identical to @Bo_Ry's but didn't post it as it was so similar (if not identical), mine also gave the correct results as per your screen capture. Comparing your results to ours tells me that there are hidden 0 values in the cells that appear empty, given the correct information to work with we could have provided a formula that would give the correct results with the actual data instead of wasting time on something that was destined to fail from the beginning.

 

[serge]

Ok, I didn't know about XL2BB I will trry to use it next time I need to show the spreadsheet, Thank you for the tip.

 

[serge]

Your mistake was posting your example in a screen capture, if you used XL2BB instead then we would have been able to see exactly what is in the cells.

I came up with a formula that was almost identical to @Bo_Ry's but didn't post it as it was so similar (if not identical), mine also gave the correct results as per your screen capture. Comparing your results to ours tells me that there are hidden 0 values in the cells that appear empty, given the correct information to work with we could have provided a formula that would give the correct results with the actual data instead of wasting time on something that was destined to fail from the beginning.

Hi Jason,

There is NO 0 hidden values in my tables, the empty cells ARE empty, what I'm looking for exactly, is what I show in post #1.

Does XL2BB work with Excel 2007 ? if yes how do I use it ?
Thank you.