Combine & Merge Data from Multiple Rows

**rockstar1989** · 08-22-2022, 01:06 PM

Hi,

I need help in merging and combining data from multiple rows and deleting duplicates. I have multiple number of contact information (rows) against single e-mail address (primary value). I need Excel to look for first row of primary value (email address) and fill out missing values (columns) from other rows. So the first row of contact information would be my key record, complete this record from other rows and delete duplicate/other rows.

Please see the attached file for better understanding of raw data and example output. Example.xlsx

Thanks

**oeldere** · 08-22-2022, 01:39 PM

Probably I can't help you with your question.

Cook Woods is in Oregon and NY.

In the result you refer tot Oregon.

What is the criteria for that?

Another tought.

Is there no other data where you can pull all information instead of combining data.

**beyond Excel** · 08-22-2022, 02:01 PM

Hi. I show you one way:

Please Login or Register  to view this content.

**JEC.** · 08-22-2022, 02:37 PM

One more variant

Please Login or Register  to view this content.

**rockstar1989** · 08-22-2022, 09:21 PM

This looks wonderful. I have data in 22 columns (A to V) and the key value (email address) is in column D. How do I edit this code as per these specifications? Also I want data to be extracted in Sheet2 range A1.

Many thanks for your help.

**rockstar1989** · 08-22-2022, 09:22 PM

Originally Posted by beyond Excel

Hi. I show you one way:

Please Login or Register  to view this content.

This looks wonderful. I have data in 22 columns (A to V) and the key value (email address) is in column D. How do I edit this code as per these specifications? Also I want data to be extracted in Sheet2 range A1.

Many thanks for your help.

**jindon** · 08-22-2022, 09:41 PM

Please Login or Register  to view this content.

**rockstar1989** · 08-22-2022, 09:52 PM

Originally Posted by jindon

Please Login or Register  to view this content.

This isn't copying missing data from other rows. For example it has not copied Title (column E) and Contact (column I) from other rows.

**jindon** · 08-22-2022, 09:54 PM

Should,

I changed the code as I realzed I misread the problem, so you may using the code one before I changed.

**rockstar1989** · 08-22-2022, 10:04 PM

Originally Posted by beyond Excel

Hi. I show you one way:

Please Login or Register  to view this content.

Yes sure. I just want this code to be changed as per these specifications: Data in 22 columns (A to V), Key value (email address) is in column D, Data to be extracted in Sheet2 range A1.

**rockstar1989** · 08-22-2022, 10:05 PM

Originally Posted by JEC.

One more variant

Please Login or Register  to view this content.

This one also works fine.
I just want this code to be changed as per these specifications: Data in 22 columns (A to V), Key value (email address) is in column D, Data to be extracted in Sheet2 range A1.

**jindon** · 08-22-2022, 10:09 PM

Originally Posted by jindon

Should,

I changed the code as I realzed I misread the problem, so you may using the code one before I changed.

...........

**rockstar1989** · 08-23-2022, 12:33 AM

Originally Posted by jindon

...........

Seems good. But the code is still running after 15+ minutes on my original file of 82k rows. Is there any faster solution for this?

**rockstar1989** · 08-23-2022, 12:45 AM

Originally Posted by jindon

...........

30 minutes and the code has still not responded

**jindon** · 08-23-2022, 01:25 AM

See if this is faster.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

**rockstar1989** · 08-23-2022, 01:53 AM

Originally Posted by jindon

See if this is faster.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Thanks for your help. The first code gave "Type Mismatch" error on line 8 highlighting this > If a(i, ii) <> "" Then

The second code gave result within 5 seconds but some values are wrongly inputted. Two rows of same email address had no values in title column but the output result had title inputted

**jindon** · 08-23-2022, 02:00 AM

If type mismatch, you may have error value somewhere.

Please Login or Register  to view this content.

Can not replicate the "wrong result".

Please Login or Register  to view this content.

**oeldere** · 08-23-2022, 02:09 AM

@rockstar1989

Please don't quote whole text, just the part you refer to.

If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @Oeldere).

**rockstar1989** · 08-23-2022, 02:38 AM

@jindon

Both codes are working but the results aren't fine. Check sample file attached. I have highlighted one of the wrong results. Column J and K are blank but the result gives values in column J and K.

**jindon** · 08-23-2022, 02:45 AM

OK, change to

Please Login or Register  to view this content.

**beyond Excel** · 08-23-2022, 05:02 AM

Originally Posted by rockstar1989

This looks wonderful. I have data in 22 columns (A to V) and the key value (email address) is in column D. How do I edit this code as per these specifications? Also I want data to be extracted in Sheet2 range A1.

I show you the small adjustment:

**rockstar1989** · 08-23-2022, 06:42 AM

@jindon

Almost there. Please check attached file, just two issues:

- Yellow highlighted: when there is a value in first row then it should not replace it with the value in second row because first row data is the most recent one so I have to keep it.
- Green highlighted: when there isn't any value in first row, it should take from the second row but this one has taken values from the third row.

Thanks again for your help!

**rockstar1989** · 08-23-2022, 06:43 AM

@beyond Excel

This one is taking too long to perform. I waited for 15 minutes but it wasn't responding.

**jindon** · 08-23-2022, 06:51 AM

try change to this

Please Login or Register  to view this content.

EDIT typo
And another typo...

**rockstar1989** · 08-23-2022, 10:42 AM

@jindon

This one is perfect! Thank you so much!

**beyond Excel** · 08-23-2022, 01:23 PM

Originally Posted by rockstar1989

... on my original file of 82k rows.

My bad!... I hadn't read this detail about the 82K rows. Let's go for my last proposal:

Please Login or Register  to view this content.

**rockstar1989** · 08-23-2022, 02:15 PM

@beyond Excel

It gives Type Mismatch error on this line > If Mat2(k, j) = "" Then

**beyond Excel** · 08-23-2022, 02:47 PM

Originally Posted by rockstar1989

@beyond Excel

It gives Type Mismatch error on this line > If Mat2(k, j) = "" Then

That's because you have something "weird" in some cell: Is there any way I can see your real data?...

Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Re: Combine & Merge Data from Multiple Rows

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Merge/Combine data in a column of many rows into one cell separated by a space

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions