Delete duplicate and merge data in one row

[fnjtravel]

Aloha, I have a new project that needs macro code. Your help is very much appreciated. We have a spreadsheet with duplicate accounts meaning two or three rows with the same account but different information. We want to use only one row for one account and move the new data from the same account to one row only to the right and delete the duplicates. Can someone please help me with this?I read so many post and I tried some of them but it only delete the duplicate row and not copying the new data from that row to one row only. Also, the other code I tried was retaining only the current or old data. Actually, to elaborate more, I want to get the new data in each cell of the same account in multiple row and move it in one row to the right only and delete the duplicate in that same account. Is this possible to do? Please help. Thanks.

[rylo]

Hi

Sure it can be done but we need more detail on your file structure.

Can you please put up a sample workbook, with the first sheet showing the before situation, and the second sheet that after. That way we can see what data is common to the rows, and what data has to be consolidated.

rylo

[MatrixMan]

Hi - as rylo says, this is easier if we know you exact structure, but I've assumed you've just got 2 columns (account & "info") ... in which case, this should do it:

Please Login or Register  to view this content.

[fnjtravel]

Hi Rylo and MatrixMan. Thank you for your post. I'm sorry if I couldn't reply soon due to time difference. Anyway, I will attached a sample spreadsheet with just 4 records and hope that will help. I actually have more than 1000 of records with more than 20 columns but some of them has duplicate and as I mentioned I want to move the new data to the right of the one account only. Hope that will help. Thanks a lot.

[rylo]

Hi

Unless I'm missing something, I can only see 3 records. You also don't have an output sheet, so I'd have to make some guesses on what is the default columns to join.

Can you please update to show what the output should be for this example.


rylo

[fnjtravel]

Hello, I attached again maybe more than four records with the output on the bottom what we would like to be. On the title, there is actually a duplicate because of the data that we want to move to a single line only and we want to keep the title of each column too that we transfer. We want to look only on the duplicate account number with the same information which we want to be deleted. If there is no duplicate account then we want to keep as is and ignore the empty field. I hope you can help me with this as this one is getting too complicated. Thanks a lot.

[fnjtravel]

Hello again. Thank you so much for your help. Please ignore the previous attachment as I never save the output to Sheet 2. I hope this one will help. I actually have more than 1000 records but I only gave you some of it. Is there any way to read until the end of file? I hope you can help me. Thanks.

[rylo]

Hi

Can you please confirm that the matching is to occur on columns A-M inclusive, and that columns N-U are to be appended.


rylo

[fnjtravel]

Hi Rylo, thanks. Yes I confirm. It doesn't need to be on those columns but we want all addition to the right. Thanks a lot for this.

[MatrixMan]

Hi - perhaps I'm just being thick ... but could you double-check your draft output and confirm? I think the column labelled "TRANS_NUMBER_2008_ALOHAUWCAMP_ALLPROCESSINGACCOUNTS_ALLFUNDRAISINGACCOUNTS" is missing in the 2nd iteration of appended columns as there is no consistent pattern in the column headers because of this. If the columns to match are A-M then the headers for subsequent columns should repeat from TRANS_NUMBER_2008_ALOHAUWCAMP_ALLPROCESSINGACCOUNTS_ALLFUNDRAISINGACCOUNTS ... out to however many columns are repeated; which it doesn't ...

[fnjtravel]

Hi. Sorry for the confusion. I actually wanted to append the column label also together with the data to the right of the original row. Is that possible to do? Is there any way to copy the column label to the right with the append data? Can we delete the duplicate data from the account only and not including the column labels? I hope I answer you question correctly and again I apologize as I don't know that much of macro codes. I found a code on how to transfer the same account to one row to the right which it works but the column label doesn't transfer and there are duplicates in the row still. I know with a little trick on the code it will give me an output how I wanted it the easiest way possible. Please help and thanks a lot for your help. I really appreciate it.

[MatrixMan]

hi .. I understand what you say you're trying to do, but the output you provided in the sample file doesn't match ... at least as far as I can tell anyway. Everything you describe is absolutely possible, but please double check where your columns repeat from the matched rows because they are not consistent in your sample output. If the matching is done on A-M then which cols repeat for the matched rows?

[fnjtravel]

Hi again.
1. From the sample sheet, Row 1-3 consists of only one account but different transactions.
2. Between those rows I want to check for any duplicate entry MOST IMPORTANT ON THE COLUMN below:

INDACCOUNTNUMBER
INDLASTNAME
GIFT_IND_2006_ALLPROCESSINGACCOUNTS_ALLFUNDRAISINGACCOUNTS
EMP_20100924_NAME1
EMP_20100924_NAME2
EMP_20100924_ACCOUNTNUMBER
EMP_20100924_JOBTITLE1
ADR_IND_1500001_ADDRESSLINE1
ADR_IND_1500001_ADDRESSLINE2
ADR_IND_1500001_CITY
ADR_IND_1500001_ADDRESSTYPE
ADR_IND_1500001_STATEORPROV
ADR_IND_1500001_ZIPPOSTALCODE.
For the NEW transaction data such as TransactionNumber…….etc to the end of the column, I will need
a duplicate column labels for those but if there are duplicate data in the same account with those column
labels I want it to delete the duplicate data and make the cell empty instead of deleting the Column.

3. Whatever the new data coming from duplicate row, we want to merge it to the right with the column labels on it on top.
5. Based on what I am figuring out on the output, there is no consistent data where. All we wanted to is to transfer the new transaction which will not duplicate the first row to one row only and to the right with the column labels included on top of it.

I hope this information will help you. Sorry for this so messy and complicated answer but I guess I give you the right information. Thank you again and please be patience.

[rylo]

Hi

In your example file create 3 new sheets called sheet3, sheet4 and sheet5.

Put the code below into a general module, and run. The output will be in sheet3.

Note that in your data, rows 2 and 3 are the same, but row 4 is different. Check the items in column D.


rylo

Please Login or Register  to view this content.

[fnjtravel]

Hi Rylo, thanks a lot. The code do exactly what we wanted to be in the output as we test it. We never try on the original file yet but it looks like what we wanted to be. Excellent job Rylo and I really appreciate your hard work for this. Thanks again.

[Rajbrinder]

Hi Rylo

I am also having a similar problem , but number of columns in my excel file are Accountid, filed1,field2,field3. How can I modify this code so it appends the duplicate information in righmost 3 columns. Sorry for being ignorant, where are we supposed to run this code.


Thanks

[Leith Ross]

Hello Rajbrinder,

Welcome to the Forum!

Please start a new thread by reposting your question in the Programming Forum. Be sure to use a clear and concise title to describe your problem. Post any code you have questions about or a workbook if your question relates to the layout of your data.

Sincerely,
Leith Ross

[fnjtravel]

Hi Rylo,

The code works as I tried it but how about if there are more columns to filter? I tried to change the column letter to the letter I want to filter but somehow it stops on the filter column itself and it doesn't merge to the right. It also truncate the original which doesn't have duplicate. Attached is the file: original is testmerge2 and the output I want is the sheet1. I really need this asap and sorry but my macro is not that good yet although I am trying to learn. I hope you can help me with this. The code you gave me helps but with little changes maybe it will run, right?

Please Login or Register  to view this content.

[VBA Noob]

fnjtravel,

Please read forum rules below and then add code tags. Also I suggest following Leith request to start a new thread as this one is getting hard to follow. You can add a link to the new thread. e.g

http://www.excelforum.com/newreply.p...te=1&p=1983585

VBA Noob

[rylo]

Hi

In your expanded file, what columns are you wanting to extract as the unique combinations? A-M? Or something else?

rylo

[fnjtravel]

Hi Rylo,

Thanks for your fast response. I want to delete the duplicate from A-V, keep the original row and the original from W - AA in each duplicate row will need to move to the right of the original row which is the first one. So eventually, it is the same as before but this one has more data on it. Thanks a lot. I appreciate your help and time for this.

[rylo]

Hi

I've updated the code to match the example workbook you have given. See how it goes.

Please Login or Register  to view this content.

rylo

[fnjtravel]

Hi Rylo,

Thanks for the code. I actually try to play around the code and get the same code as you do. Thanks a lot. The only problem now is it doesn't read the duplicate until the end of file. I guess it does read only until row 10 then the rest is just bypass it. Is there any way to resolve it, to read until the end of file the duplicate? I am trying in my own too this one and see if I will get it right again. I have a file of 445 rows and to delete the duplicate I will only have 178 rows supposed to be. PLease advise. Thanks.......

[rylo]

Hi

Will there always be an entry in column A of the data sheet? Using your work file, I've based things on there always being an entry in column A of testmerge1 for every item.

If this is not the case, is there a column that will always be filled?

rylo

[fnjtravel]

Hi Rylo,

Yes. Column A always have an entry and sometimes our spreadsheet has 1000 + of accounts depends on the data mining request. On the original terstmerge that I have, it does check all the rows which is 100+ accounts but this one doesn't. I tried to check the code but I don't see any thing there. Of course, I am not that good in macro but just trying to learn from it. Hope you can help me again. Thanks a lot.

[rylo]

Hi

Can you build an example file that replicates your problem with the last lot of code. I'll have a look.....

If it is too large to attach to this site, then get one of the free storage sites, put your file there, and provide the details on how to got to the file (URL, IDs, password etc)

rylo

[fnjtravel]

Hi Rylo,

I tried to do it my own but I really couldn't do it. Can you please help me??? Attached is the testmerge that I am trying to do and there are more than 300
rows on it. I will not post the outcome for all of them but here is what I want to do:

• From A to Z are sometimes duplicate and so I want to delete the duplicate
• From AA to AE are all unique and I want each duplicate row with unique data to move to the farthest next right column available on the original row
• But I want to read all the rows on file and the code I use doesn't read it. Please help!

Importantly, I want to read from A to Z for the duplicate rows and merge the rest of the columns to the right for a new data from each duplicate column to the original row.

Thank you so much for your help. I really appreciate it. Here is the original code you sent me that I am tryign to play to make it work..Sorry I am not good in macro...

Please Login or Register  to view this content.

[rylo]

Hi

See how the attached goes.

rylo

[fnjtravel]

Hi Rylo,

Thanks for your fast reply to my post. I tried it and it does work however there is always a duplicate. Why is it like that? Does it bypass some of the rows sometimes? I saw that there are maybe less than 10 duplicate to the testmerge file. Sorry but you are almost there. Is there any way to read all the data to the end of file but use the same code? Please help! Thanks again...

[rylo]

Hi

Can you identify the duplicates. I ran it through, and based on column A there are 8 duplicates. However, as you go across the row, you should find that column X, Y and Z are all different. As you are using columns A-Z as your unique combinations, then these make them unique items.

rylo

[fnjtravel]

Hi Rylo,

You are so cool and awesome! It does work the code with a little twist on it all duplicate gone. Thanks. For a person like me who doesn't know macro I will never find out. Thanks for your patience and understanding and especially the time you volunteer for this code. I really appreciate it and hope you will help more people out there who doesn't know or still learning how to use macro and trying to understand the language. God Bless and take care.........

Jenny

[lpsarathy]

Hi Rylo,
I hv gone thru' the posts. I hv a very similar kind of reqmt as of FNJTravel. My reqmt is given below :
(a) Have data from A to S. Some of the data are unique & some are duplicate.
(b) I will check for a unique value in column D (assume it is employee ID).
(c) If I found a duplication in Column D value, I will retain the 1st row. And add the value of Column C, E, F (add the values in both the rows in the columns C,E,F). For columns, G, H, I, I will append the texts by inserting a "/". For example, for employee ID 121, i find 2 records. I will retain the first record as it is and from the 2nd record, I will add the value of Record1 + Record2 for column C, E, F and append the value of Record1 & Record2 as Programme Manager / Delivery Manager. Here the Programme Manager is the value in REcord 1 & Delivery Manager is from Record 2.
(d) Finally remove the duplicate record.

For FNJTravel, you have appended the data from the duplicate record, at the end of the first record. For me, I need to add the values in appropriate column & delete the duplicate record. thats it.

I have attached a sample file. The expected result sheet has marking on my exact requirement.

Hope you could help me out in achieving this.

Regards,
Partha

[NBVC]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.