Please Help me simplifying this silly formula

[jbesclapez]

Hello,

I made a stupid formula. It works but as my data increases it gets impossible. I need a better version of it. No VBA plz.

=IF(H20=1,I20,IF(H20=2,IF(J20=1,I20&"--"&I21,K19),IF(H20=3,IF(J20=1,I20&"--"&I21&"--"&I22,K19),IF(H20=4,IF(J20=1,I20&"--"&I21&"--"&I22&"--"&I23,K19),IF(H20=5,IF(J20=1,I20&"--"&I21&"--"&I22&"--"&I23&"--"&I24,K19),IF(H20=6,IF(J20=1,I20&"--"&I21&"--"&I22&"--"&I23&"--"&I24&"--"&I25,K19),IF(H20=7,IF(J20=1,I20&"--"&I21&"--"&I22&"--"&I23&"--"&I24&"--"&I25&"--"&I26,K19),"Superieura7")
))))))

Basically the formula concatenates N amount of cells that are below each other.
The N amount to concatenate is on another cell in the same line.
You can have many groups of N.
The X amount helps you differenciate the groups as a new group always starts with 1...to N

In the example below, the tricky one is the group N=5 because there are 2 groups : The first one giving the result KLMNO and the second one PQRST

N	X	data	Expected Result
1	1	A	A
1	1	B	B
2	1	C	CD
2	2	D	CD
3	1	E	EFH
3	2	F	EFH
3	3	H	EFH
1	1	I	I
1	1	J	J
5	1	K	KLMNO
5	2	L	KLMNO
5	3	M	KLMNO
5	4	N	KLMNO
5	5	O	KLMNO
5	1	P	PQRST
5	2	Q	PQRST
5	3	R	PQRST
5	4	S	PQRST
5	5	T	PQRST

<tbody>
</tbody>

How can I solve that easily knowing that N can be above 60

Thanks

PQRST

<tbody>
</tbody>

 

[Marcelo Branco]

What Excel version are you using?
I'm asking because in Excel 365 the function TEXTJOIN is available and a simple formula can be created. Otherwise, you need VBA.

M.

 

[jbesclapez]

What Excel version are you using?
I'm asking because in Excel 365 the function TEXTJOIN is available and a simple formula can be created. Otherwise, you need VBA.

M.

Hi Marcello,

I am not using the 365 version.

I kind of guessed your conclusion and prepared that :
Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
Result = Result & Cell.Value & Separator
Next Cell
CONCATENATEMULTIPLE = Left(Result, Len(Result) - 2)
End Function

Please note that I am using a double separator (--)

Now working on the magic VBA

 

[Marcelo Branco]

Maybe...

Function Conc(r As Range, sep As String)
    Dim rCell As Range, strAux As String
    
    For Each rCell In r.Offset(, 2).Resize(r.Value)
        strAux = strAux & " " & rCell.Value
    Next rCell
    Conc = Replace(Application.Trim(strAux), " ", sep)
End Function

	A​	B​	C​	D​
1​	N​	X​	data​	Expected Result​
2​	1​	1​	A​	A​
3​	1​	1​	B​	B​
4​	2​	1​	C​	C--D​
5​	2​	2​	D​	C--D​
6​	3​	1​	E​	E--F--H​
7​	3​	2​	F​	E--F--H​
8​	3​	3​	H​	E--F--H​
9​	1​	1​	I​	I​
10​	1​	1​	J​	J​
11​	5​	1​	K​	K--L--M--N--O​
12​	5​	2​	L​	K--L--M--N--O​
13​	5​	3​	M​	K--L--M--N--O​
14​	5​	4​	N​	K--L--M--N--O​
15​	5​	5​	O​	K--L--M--N--O​
16​	5​	1​	P​	P--Q--R--S--T​
17​	5​	2​	Q​	P--Q--R--S--T​
18​	5​	3​	R​	P--Q--R--S--T​
19​	5​	4​	S​	P--Q--R--S--T​
20​	5​	5​	T​	P--Q--R--S--T​

Formula in D2 copied down
=IF(B2=1,Conc(A2,"--"),D1)

Hope this helps

M.

 

[jbesclapez]

Maybe...

Function Conc(r As Range, sep As String)
    Dim rCell As Range, strAux As String
    
    For Each rCell In r.Offset(, 2).Resize(r.Value)
        strAux = strAux & " " & rCell.Value
    Next rCell
    Conc = Replace(Application.Trim(strAux), " ", sep)
End Function

	A​	B​	C​	D​
1​	N​	X​	data​	Expected Result​
2​	1​	1​	A​	A​
3​	1​	1​	B​	B​
4​	2​	1​	C​	C--D​
5​	2​	2​	D​	C--D​
6​	3​	1​	E​	E--F--H​
7​	3​	2​	F​	E--F--H​
8​	3​	3​	H​	E--F--H​
9​	1​	1​	I​	I​
10​	1​	1​	J​	J​
11​	5​	1​	K​	K--L--M--N--O​
12​	5​	2​	L​	K--L--M--N--O​
13​	5​	3​	M​	K--L--M--N--O​
14​	5​	4​	N​	K--L--M--N--O​
15​	5​	5​	O​	K--L--M--N--O​
16​	5​	1​	P​	P--Q--R--S--T​
17​	5​	2​	Q​	P--Q--R--S--T​
18​	5​	3​	R​	P--Q--R--S--T​
19​	5​	4​	S​	P--Q--R--S--T​
20​	5​	5​	T​	P--Q--R--S--T​

<tbody>
</tbody>

Formula in D2 copied down
=IF(B2=1,Conc(A2,"--"),D1)

Hope this helps

M.

Thanks, yuor solution works. THumbs up.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.