| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Have data with multiple records associated to same ID
My Data Situation:
A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Have data with multiple records associated to same ID
How many subsets are you likely to have (maximum)? Are the values DLL,
CND etc in column C, or are they tagged on to the end of the ID in column B? Pete On Feb 12, 2:42*pm, Dccp_WV wrote: My Data Situation: A * * * * * * * * * * * * * * *B 33007014220000 * * * *33007014220000 * *DLL * * * * * * * * * * * * * * * * 33007014220000 *CND * * * * * * * * * * * * * * * * 33007014220000 *CBL Need to get it like this: A * * * * * * * * * * * * * *B 33007014220000 * * *CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Have data with multiple records associated to same ID
i would like the list of values CND,DLL to be in column C. And it could be
anywhere from 2 records per ID to 10 records i'm not sure. "Pete_UK" wrote: How many subsets are you likely to have (maximum)? Are the values DLL, CND etc in column C, or are they tagged on to the end of the ID in column B? Pete On Feb 12, 2:42 pm, Dccp_WV wrote: My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Have data with multiple records associated to same ID
One way. Put in c2 and copy downchange to valuesdelete col B
=IF(LEN(A2)<2,"",RIGHT(B2,3) & " " &RIGHT(B3,3)& " "& RIGHT(B4,3)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Dccp_WV" wrote in message ... My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Have data with multiple records associated to same ID
Hi,
Download and install the following add-in - http://www.download.com/Morefunc/300...-10423159.html Suppose your data is in range B5:C7. In B11, you have 33007014220000. In cell C11, enter the following array formula (Ctrl+Shift+Enter) =SUBSTITUTE(TRIM(MCONCAT(IF(1*LEFT($C$5:$C$7,LEN(B 11))=B11,RIGHT(C5:C7,3),"")," "))," ",",") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dccp_WV" wrote in message ... My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Have data with multiple records associated to same ID
Ashish,
I tried this route and it didn't work. I don't have admin privelages on this machine so i don't think the add-on installed correctly. I tried the previous entry and it worked for each individual record but there's no way to do it for multiple records. The problem is there isn't a defined number of dup records for each individual key. One unique key could have as many as ten seperate log types or as few as one. How do you tell EXCEL to look and match the duplicate keys and list each unique record attached to it in the format i have specified? "Ashish Mathur" wrote: Hi, Download and install the following add-in - http://www.download.com/Morefunc/300...-10423159.html Suppose your data is in range B5:C7. In B11, you have 33007014220000. In cell C11, enter the following array formula (Ctrl+Shift+Enter) =SUBSTITUTE(TRIM(MCONCAT(IF(1*LEFT($C$5:$C$7,LEN(B 11))=B11,RIGHT(C5:C7,3),"")," "))," ",",") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dccp_WV" wrote in message ... My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Have data with multiple records associated to same ID
I counted the number of duplicates, it ranges from 2-11.
"Dccp_WV" wrote: Ashish, I tried this route and it didn't work. I don't have admin privelages on this machine so i don't think the add-on installed correctly. I tried the previous entry and it worked for each individual record but there's no way to do it for multiple records. The problem is there isn't a defined number of dup records for each individual key. One unique key could have as many as ten seperate log types or as few as one. How do you tell EXCEL to look and match the duplicate keys and list each unique record attached to it in the format i have specified? "Ashish Mathur" wrote: Hi, Download and install the following add-in - http://www.download.com/Morefunc/300...-10423159.html Suppose your data is in range B5:C7. In B11, you have 33007014220000. In cell C11, enter the following array formula (Ctrl+Shift+Enter) =SUBSTITUTE(TRIM(MCONCAT(IF(1*LEFT($C$5:$C$7,LEN(B 11))=B11,RIGHT(C5:C7,3),"")," "))," ",",") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dccp_WV" wrote in message ... My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Have data with multiple records associated to same ID
I then sorted the entire list by the number of duplicate reocrds and used an
if statement to sort out the duplicates and attached the Concentate fromula to populate on the cells that met the criteria. =IF(AND(A3=A2,A4=A3,A5=A4),CONCATENATE(B2&",",B3&" ,",B4&",",B5),"") "Dccp_WV" wrote: I counted the number of duplicates, it ranges from 2-11. "Dccp_WV" wrote: Ashish, I tried this route and it didn't work. I don't have admin privelages on this machine so i don't think the add-on installed correctly. I tried the previous entry and it worked for each individual record but there's no way to do it for multiple records. The problem is there isn't a defined number of dup records for each individual key. One unique key could have as many as ten seperate log types or as few as one. How do you tell EXCEL to look and match the duplicate keys and list each unique record attached to it in the format i have specified? "Ashish Mathur" wrote: Hi, Download and install the following add-in - http://www.download.com/Morefunc/300...-10423159.html Suppose your data is in range B5:C7. In B11, you have 33007014220000. In cell C11, enter the following array formula (Ctrl+Shift+Enter) =SUBSTITUTE(TRIM(MCONCAT(IF(1*LEFT($C$5:$C$7,LEN(B 11))=B11,RIGHT(C5:C7,3),"")," "))," ",",") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Dccp_WV" wrote in message ... My Data Situation: A B 33007014220000 33007014220000 DLL 33007014220000 CND 33007014220000 CBL Need to get it like this: A B 33007014220000 CBL,CND,DLL Is there a way to do this via formulas or code? I am doing it by hand and will take days to go through 1683 Unique records. Thanks for your help! Dan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete records when certain records have duplicate column data | New Users to Excel | |||
| Creating a Custom Data Entry Form to Update Multiple Excel Records atOnce | Excel Discussion (Misc queries) | |||
| Combining multiple data records | Excel Discussion (Misc queries) | |||
| Combining multiple data records | Excel Worksheet Functions | |||
| return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Hybrid Mode
