[Request for Macro] Merge Similar Rows to 1 Row

Hello Guys, I would just like to ask help from pro excel users especially to those familiar with VBA. I have approximately 35k entries similar to the image below. What I want to happen is to create a Macro to combine rows with similar values in the following fields: Facility, Generic Name, Price, Brand, Manufacturer, Supplier and Mode of Procurement. While the values from the Quantity field shall be combined and be reflected in the row with the earliest date. The control numbers should also be consolidated in the row with the earliest date and must be separated by comma (,). Other rows where the Quantity and Control Number was extracted should be deleted.

Please see the image if my description is unclear. I also attached in this post the copy of the file.

I want this to loop over the 35k entries.

Thank you.


Untitled.jpg

Try

Please Login or Register  to view this content.

If you are talking about the different file, no idea.

No, its similar with the file im working with. However my example is with complete data in it. My actual file have blank cells in it specifically in the Brand and Manufacturer. Please see the image how my actual data looks like.

Originally Posted by jindon

If you are talking about the different file, no idea.

Untitled2.jpg

No body want to work with picture.

If you upload a workbook with EXACT layout of raw data and the EXACT result that you want, it may help.
As long as data type are the same and showing the clear logic, dummy data and only few rows are acceptable.

Okay sorry I am new of asking these kind of things. Please give me 30 mins I'll prepare a dummy table exactly the same as the actual data.

Originally Posted by jindon

No body want to work with picture.

If you upload a workbook with EXACT layout of raw data and the EXACT result that you want, it may help.
As long as data type are the same and showing the clear logic, dummy data and only few rows are acceptable.

Confusing 9.a
You said

9. Validated Value:YES
a. If the Value in the Validated field is No, then no merging should happen even if Fields 1 - 9 are similar (including the No Value in the Validated Field)
.
.
.
When not to merge:
1. As per 9.a If the Value in the Validated Field is No regardless if similar rows are identified.

But your results include "NO" in Validated column.

I don't know what is wrong.
The results are not the same as yours.

Please Login or Register  to view this content.

Can you just edit all your posts and remove the full quote of my posts?
The forum doesn't like it.

No worries

5. If an entry has similar data (1-3, 5-9 fields) except 4 (Brand Name) because it is blank, merging is acceptable except for case that, two or more brand names matches the said entry. If it happens that it is the earliest date among the similar rows, then it shall inherit the brand name of the latter.

If you look at the row 4 of your result, it merges, but not mine.
What happens if more than 2 brands are different but the all others are the same?

That makes complicate.

Loop all the rows to find the dups with "YES", and count different brands.
When blank in brands found with other fields are the same and the brands are only one, merge, otherwise no merge.

It that what you want?

I really appreciate your time and effort. I'm sorry if I am not good at explaining. Please bear with me.

Yes I want to loop all rows to find dupes with "Yes", No counting of different brands.
Duplicates should be deleted.

I think we should separate the process of finding dupes. It think it will complicate things if we join it together with the merge process. And finding dupes should come first before merging.

When blank in brands found with other fields are the same and the brands are only one, merge, otherwise no merge.

Yes for this.

PS: If you need me to illustrate it, please tell me.

Up for this!

Whoa I was overwhelmed with the codes Thank you.

I run the codes and here's the problem's I've encountered. Please be guided by the image.

a. Cell F3 (Wrong Value): It should be 1800 rather than 1200.
b. Cell B5 (Ordering Error): po_num 2016-08-001 should come first than 2016-09-004 because 2016-08-001 has earlier date.
c. Cell C5 (Date Value is not the earliest date): The earliest date is 8/22/2016 from po_num 2016-08-001.
d. Cell F5 (Wrong Value): It should be 800 rather than 1000.
e. Cell F9 (Wrong Value): It should be 1476 rather than 664.

I don't know if this may help but I noticed an error in the quantity value if it tries to check the validated field (F3 and F9) and also when dealing with duplicates (F5). With B5 and C5, I don't know because other merges of po_num and po_date are correct.

Moreover, it created an additional sheet that I think is needed for the process, can it be deleted automatically after the macro runs? Also the 2 helper column on the rightmost part of table.

Untitled4.jpg

1) Sub test, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) GetRow peocedure, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Sugoi! It worked! I'm crying Jindon-sama. Now I'll apply this on my 35k entries. I'll make a feedback once I'm done validating.

Domo Arigato Gozaimasu!