• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Complex Concatenation Required

Ehtisham Ali

Ehtisham Ali

Member
Hi Everyone,

This time I am stuck with a concatenate requirement, I am attaching a file for the reference.

I have 3 cols and wanted to work on DeviceName and Serial. What I want is to concatenate the device name for the same devices as on left.

I have done it manually adding the formula but I need a formula for the same so that I can just drag it to the end, since I have around 4000 rows of data.

Please suggest a way or some formula.
 

Attachments

  • conc_reqd.xlsx
    11.8 KB · Views: 17
Hi Ali,
Good day...

I know someone came with more efficient idea, till than you can see the logic for 2 concatenations:

=IF(COUNTIF($A$2:$A$20,A2)>1,INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0),3)&"/"&INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0)+1,3),INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0),3))

formula will be long if we need 3.


EDIT:
As Sir Faseeh came up, you can ignore my post.
 
Hi Ali,
In addition to above, see if it helps:

=IF(COUNTIF($A$2:$A$20,A2)=1,INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0),3),IF(COUNTIF($A$2:$A$20,A2)=2,INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0),3)&"/"&INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0)+1,3),IF(COUNTIF($A$2:$A$20,A2)=3,INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0),3)&"/"&INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0)+1,3)&"/"&INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0)+2,3),IF(COUNTIF($A$2:$A$20,A2)=4,INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0),3)&"/"&INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0)+1,3)&"/"&INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0)+2,3)&"/"&INDEX($A$2:$C$20,MATCH(A2,$A$2:$A$20,0)+3,3)))))

Sorry for the length of formula.

Regards,
 
Hi Khalid,

Thnks for your enormous interest and possible solution. Since the formula is bit lengthy let me check and understand it.
 
Hi Khalid,

can u plz apply this formula to the original sheet shared and upload it, bcoz I am confused where to apply it.
 
Khalid NGO said:
Its G2:

See the orange cells in attached.
Click to expand...

Hi I have gone through your solution it is awesome for the problem I shared.

But I have shared a small part of a big one. In the current formula before applying INDEX we have used COUNTIF to check frequency of a item.

In original problem the frequency goes up to 33 so in that case concatenating 33 INDEX is not feasible.

Can we apply a Divide&Conquer approach to the problem ? I have no idea :(
 
Hi Ali ,

By now , it should be clear that the Excel CONCATENATE function cannot deal with multiple cell ranges / arrays ; every value that needs to be concatenated needs to be specified explicitly.

For your 33 items , it is impossible to have a formulaic solution , period.

If you are willing to have helper columns , it will be possible , but again , over thousands of rows , you will have to take a response hit , since your workbook will become sluggish.

VBA is the only way to go.

For this , upload a sample workbook which has a variety of data , and explain all your requirements in one go.

Narayan
 
Hello Sir,

Sorry for the inconvenience caused due to incomplete question.

Now plz find attached my complete question file containing all data.

Question is again the same as discussed earlier, I want to concatenate the "Model Name" and "Serial Number" for where the "Status Last Modified" is same.

I have added 2 orange color cols to demonstrate the required solution.

Please help me out with such a solution that for next month data I just put in the data in cols A,B and C and it will work.
 

Attachments

  • CONC_QUERY.xlsx
    46 KB · Views: 4
You must log in or register to reply here.
Back
Top