• 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.

Excel Concatenate IF

S

sonnyatoak

New Member
There has to be a way to do this on my spreadsheet. Column A has a list of part numbers. For example, part number is 1234 is listed 3 times (A1:A3) in the column A, Part 22334 is listed 5 times (A4:A8), and part 54344 is listed 6 times (A9:A14. IN column B, I have text written, but the text is different for each corresponding similar part. For part 1234 for example, cell B1 has written, "part is too long" . In cell B2, has written "meets guidelines", and so on.

What I would like to do is combine all the "texts" into one cell for only the similar part numbers (I think CONCATENATE and IF function accomplish this). However, since I have several parts numbers, how do I only combine texts for each unique part number. I only want those comments combined that have similar part numbers. So in the example above, each of the 3 part numbers (I actually have 100's but just gave 3 for the example) would have all related corresponding texts merged. But I need this done for pattern done for all unique parts. See part of the worksheet below. I could have this information on sheet 1, and results on sheet 2. Probably needs to be.

[pre]
Code: 
Part No	        Comment Text
65601918639	6 X 19 11/16 18.5D BGDC,BLK W/ GAS PROPS 2-T HAN
65601918639	DLES DRK GRY DOT-MAT SLKSCRN GLASS DEEP ELECTRIC T
65601918639	UBE,
150492022822	49 25/32 X 20 7/16 22D DRD BLK NO PROPS DRK CON,
150492022822	DRD DELUXE REAR CAP DOOR-SLAM LATCH,HIDDEN HINGE
150492022822	 75" CONTOUR NO PROPS NO HANDLE ,FORD 8 HOLES IN
150492022822	 GLASS 3/16" GL20 W/ SILK,GLASS SIZE 51.956 X 21
150492022822	.499 12" PROP SPACING,60" CURVED BOTTOM TRIM,
150492022825	49 25/32 X 20 7/16 22D DRD BLK NO PROPS DRK CON,
150492022825	DRD DELUXE REAR CAP DOOR-SLAM LATCH,HIDDEN HINGE
150492022825	 75" CONTOUR NO PROPS NO HANDLE,FORD 8 HOLES IN
150492022825	GLASS 3/16" GL20 W/ SILK,GLASS SIZE 51.956 X 21.
150492022825	499 12" PROP SPACING ,60" CURVED BOTTOM TRIM XSS
[/pre]
 
Sonnyatoak


Firstly, Welcome to the Chandoo.org forums.


I would add a couple of Columns

C2: ==IF(A2<>A3,A2,"")

Copy down


D2: =B2

D3: =IF(C1<>"",B2,D1&B2)

Copy down


E2: =IF(C2<>"",D2,"")

Copy down


Now copy Columns C & E as Values and paste over themselves

Delete Column D

Sort C&D by Column C


Voila
 
2nd stickey on the forums shows how to upload a workbook:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Ok, here is the download link to what I'm working on. Trying to reduce the number of duplicate part numbers while combining the texts into one.


https://hotfile.com/dl/175379633/5411562/sonnyatoak_file.xlsx.html
 
Instead of formula, if VBA is OK with you then assuming the following:

1. Column A and B contain data.

2. Column D and C should contain combined data.

[pre]
Code: 
Public Sub CombineText()
Dim objDic As Object: Set objDic = CreateObject("Scripting.Dictionary")
Dim varRng As Variant: varRng = Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row)
With objDic
.CompareMode = TextCompare
For i = 1 To UBound(varRng)
If .Exists(varRng(i, 1)) Then
.Item(varRng(i, 1)) = .Item(varRng(i, 1)) & Chr(10) & varRng(i, 2)
Else
.Add varRng(i, 1), varRng(i, 2)
End If
Next i
Range("C:D").ClearContents
Range("C1").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
End With
End Sub
[/pre]
 
Hi sonnyatoak,


shrivallabhja has posted a macro which would be installed in the visual basic editor (VBE). To access this, right click on the sheet tab, view code. Then, go to Insert - Module. Paste the code into this Module.


You could now close the VBE. Back in the regular Excel workbook, you can now run the CombineText macro. Bring up the Macro dialogue (Alt+F8) and select the macro, then Run. Do note that assumptions that shrivallbaha made concerning your setup and verify that it matches your actual setup. If Visual Basic seems like too much, just say so, and we'll try to find a formula solution. =)
 
Yes, Luke sounds too much. You guys are definitely Ninja's. Need to take his excel course or get book. I don't even know where to get the file back you referred to and if I did, no clue how to use VBE
 
Hi ,


The formula based solution takes time to recalculate ; is this acceptable to you ?


A VBA solution will definitely be faster.


Narayan
 
I'm not concerned about the time. Since there are over 10,000 parts/comments listed in sheet. I just need to summarize or shorten it so one part shows all the comments. I can try the VBA solution with "baby steps" guidance. I wasn't sure of the other information above or what you meant by "shrivallabhja has posted a macro which would be installed in the visual basic editor (VBE). To access this, right click on the sheet tab, view code. Then, go to Insert - Module. Paste the code into this Module."
 
Hi ,


If you don't mind the time taken , check out this file :


http://speedy.sh/9FkNh/sonnyatoak-file.xlsx


What I did was take the Routing numbers from the routings tab to Sheet4 , remove duplicates , and introduce the formulae in columns B through G. I have assumed that there will not be more than 6 entries per routing number ; if , in fact , there are more , then you wiill have to copy the formula into those additional columns.


The final step will be to concatenate all these columns by a simple =CONCATENATE(...) formula.


Narayan
 
yes, I see what you did and it got rid of duplicate part number. I should be able to do the next step; Concatenate.


I think my brain is fried. I did this once. Which cell/column do I do the formula, the last one and select the cells, then copy and paste?
 
Hi ,


Use any free column , say column M , and in cell M2 have any one of the following :


=B2&C2&D2&E2&F2&G2


=CONCATENATE(B2,C2,D2,E2,F2,G2)


Copy either of them down as far as your routing numbers go. I am again assuming that you have only 6 repetitions of a routing number , so that only columns B through G have text in them. If there are more repetitions , you will have to extend the above formulae to include the additional columns.


Narayan
 
By George, I think I done it! Thanks everyone who helped. This is a great forum. I will recommend to others to join.
 
For some reason, some of the cells are missing a character. Not sure why since the majority is correct. Should I try a entering the Concatenate without copy and pasting. Only problem is I have 4000 lines to check. Do you see anything here. I highlighted a row 4 in gray as example. Missing "t" in bracket. I also learned that the Concatenate cell is limited to 80 characters for our system so need to put overflow in another cell below with the part listed. Is that possible?


https://hotfile.com/dl/175410841/3f01b1a/routing_line_comments_10_8_121.xlsx.html


This was my original one with Concatenate formula. The other is a copy a paste.


https://hotfile.com/dl/175412276/7f4bf08/Copy_of_sonnyatoak_file.xlsx.html
 
Hi ,


There is no problem !


As far as I can see , the routing number concerned has 10 entries against it. This means that the formula needs to be as follows , to include all the 10 entries :


=B3534&C3534&D3534&E3534&F3534&G3534&H3534&I3534&J3534&K3534


The T of the word BRACKET is in the 8th entry ; if your formula is as follows :


=B3534&C3534&D3534&E3534&F3534&G3534&H3534


it will not include the entries 8 , 9 and 10.


A more important problem I saw is that you cannot depend on Excel to remove duplicates completely. Try this on your file and see.


1. Copy the entire column A to a new sheet.


2. Select the data and go through Excel's Remove Duplicates feature.


3. With the trimmed down data in column A , in column B , put in the formula :


=(A2=A3)


I am assuming that the data starts from A2.


If the Remove Duplicates feature has worked , then all the values from the above formula should display FALSE ; however , if anywhere you see TRUE , it means there are still duplicates in the list.


Select the data from that cell downwards , and repeat the Remove Duplicates action.


Do this till all the cells in column B show FALSE.


Narayan
 
I check that out. Is there a way for the Concatenate cell to limit to 80 characters and put overflow into another cell? Our system for entry is limited to 80 characters so need to put overflow in another cell below or to the right with the part listed. Is that possible?
 
Hi ,


This is a new requirement !


Do you mean to say that after you have concatenated all the text for one routing number together in one cell , you then want the full text to be split up into 80-character chunks ?


If this is so , then why do you want to conactenate in the first place ? As it stands , each of the entries against any particular routing number is around 50 characters in length ; once you have all of these 50-character text strings in their respective cells in one row , why not retain them ? Why concatenate , and then again split ?


Since your worksheet is already slowing down due to the formulae , again splitting the concatenated text into 80-character chunks will make Excel slow down even more ; VBA might be the best way.


What do you say ?


Narayan
 
Your right. Of course they didn't tell me until after I we did this. But it did eliminate over 8000 part entries. Now I need to manually review the Concatenated cells and correct any words that are split.


Thanks for all your help so far. I think all that is left is spot checking.
 
I think I was able to eliminate the duplicate routing numbers on Sheet 3. I could use the Concatenate formula to combine columns B thru G, but the system I need to export this to only can handle 80 characters. For those routing numbers, I will have to show more than once to accommodate this, right? On sheet 1, you will see the character count. Is there a way to combine up to 80 characters, and have remaining characters in next field?


https://hotfile.com/dl/176088448/57ca118/Routing_sheet.xls.html


Thanks
 
You must log in or register to reply here.
Back
Top