Macro to loop through rows, if criteria is met then certain action is performed

Hi guys. I need to have a quite clever macro to loop through some rows and if certain criteria is met then perform certain actions.

Information on the spreadsheet is a on going and ever growing (row wise) master list so over period of time more and more lines will be added to it.Currently its more than 2000 lines. About 30 to 150 lines is added each day or so. Horizontally information range on the spreadsheet is from Col A to Col J. Cells in columns C and G are used to determine if criteria is met. If criteria is met then cells in column G and I will be manipulated. I will try to give plenty of examples to try to explain what I need to achieve.

Criteria.
First objective is to is to figure out if "No" duplicate line, "Semi" duplicate line or "Full" duplicate line exists.
So macro should loop through columns C and column G to determine if one of these three might exist. I imagine the loop trough happens in from bottom to top.

No duplicate line - cell in column C is unique (it does not equals to any other cell in column C). Looking cell in column G is not required.
Example:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026688810------------RN-207414, RN-207415, RN-207416------------------------5
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207392----------------------------------------------------------1
181026691310------------RN-207400, RN-207401, RN-207402------------------------7
181026689510------------RN-207397----------------------------------------------------------1
181026689610------------RN-207399----------------------------------------------------------1
181026716910------------RN-207368, RN-207369, RN-207370------------------------2
181026688710------------RN-207399----------------------------------------------------------1
181026688810------------RN-207414----------------------------------------------------------1

none of the cells in col C is duplicate.




Semi duplicate line - cell in column C is duplicate (it equals to some other cell in column C. Now we are comparing cell value in column G (lowest row of the duplicate against the values of the upper duplicates. If the upper duplicates do not contain the value of lowest duplicate cell we then have a semi duplicate .
Example1:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026691210------------RN-207414, RN-207416-----------------------------------------4
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207392----------------------------------------------------------1
181026691310------------RN-207400, RN-207401, RN-207402------------------------1

Edit: notice the red ones in column C are duplicates. In column G there are no duplicates

Or
Example2:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207209----------------------------------------------------------1
181026699410------------RN-207210----------------------------------------------------------1
181026699410------------RN-207211----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1

Edit: notice the red ones in column C are duplicates. In column G there are no duplicates



Full duplicate line - cell in column C is duplicate (it equals to some other cell in column C . Now we are comparing cell value in column G (lowest row of the duplicate) against the values of the upper duplicates. If the upper duplicates contain the value of lowest duplicate we then have a Full duplicate .
Example1:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026691210------------RN-207414, RN-207415, RN-207416------------------------4
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207415----------------------------------------------------------2
181026691310------------RN-207400, RN-207401, RN-207402------------------------1

Edit: notice the red ones in column C and G are duplicates.

Or
Example2:
-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1

Edit: notice the red ones in column C and G are duplicates.


Action taken if criteria is met
Action taken if criteria is met

Depending of the outcome of criteria check:
1) there is no action taken
2) certain rows are combined together and then others will be deleted.
3) certain rows are deleted


No duplicate line - in this scenario no action is taken
All the rows will be left as they were


Semi duplicate line- there are some actions to be taken with cells in column G and Column I. Column G contains certification numbers. 90 percent of the time they start with letters RN. Column I contains quantities ie numbers. So now we need to combine all the information of column G and I. It should be done on the lowest row of these duplicates. So all the certification numbers should be combined together in this following style if possible: RN-xxxx, RN-xxxx, RN-xxxx, RN-xxxx,. So comma and space would be used. Other styles could be used as well if comma and space is not be possible. Now with column I the numbers should just be summed up. All these should be done in that last row of respective semi duplicate. Alternatively a new row can be created to the to the end of the list (first available empty row) and get the information there. But then all the information in other columns should be carried along as well. Now all the duplicate rows except that last one should be deleted.

Continues with POST 2

Example1
The following should change:
-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026691210------------RN-207414, RN-207416-----------------------------------------4
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207392----------------------------------------------------------1
181026691310------------RN-207400, RN-207401, RN-207402------------------------1


to this:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207414, RN-207416, RN-207392------------------------5
181026691310------------RN-207400, RN-207401, RN-207402------------------------1


Example2:
The following should change:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207209----------------------------------------------------------1
181026699410------------RN-207210----------------------------------------------------------1
181026699410------------RN-207211----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1


to this:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207209, RN-207210, RN-207211------------------------3
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1


Full duplicate line- all the duplicate lines should be deleted, except the top one.

Example1:
The following should change:
-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026691210------------RN-207414, RN-207415, RN-207416------------------------4
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207415----------------------------------------------------------2
181026691310------------RN-207400, RN-207401, RN-207402------------------------1


To this:
-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026691210------------RN-207414, RN-207415, RN-207416------------------------4
181026691110------------RN-207394----------------------------------------------------------1
181026691310------------RN-207400, RN-207401, RN-207402------------------------1

Or
Example2:
The following should change:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1

to this:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1

I hope this sort of macro is possible. For the time being I'm doing all this manually. First I copy over some new information to this master list. Then with a help of conditional formating I have all duplicates in column C highlighted in red. From there its using filters, copy, paste typing. deleting rows etc. I hope someone knows how to achieve this with macro. Feel free to use any method. You can also use helper cells from column K onwards etc. I will attach spreadsheet that has the real data (shortened version of it) with example before and after sheets. If you run your test macro on sheet "before" and then alternate couple times between sheets "before" and "after", then everything highlighted in purple should look more or less the same. I have created sheet "after" manually of course. On those sheets consider anything above the green line as a master list and anything below it as newly added information.


Any help is very appreciated.



Cheers
Rain

Thank you for the update.
There is a small bug with it. After running the macro the results should look exactly the same as on "after" sheet. If you look column K where I have numbered the comments, number 1 and 4 are missing. With your previous iteration that was the not the case, so it must be to do with the new conditions I presented. I have tried to understand there whether there is a some sort of conflict of where I have asked two different actions in same situation and I cant figure this out. I have spent the last 45 minute doing and it still puzzles me. Can you have a look and see If you spot something where its possibly conflicting?

Regards where to put the results. I kind of now like the idea getting having the results on a separate sheet. But can we do it this way that it would be a permanent sheet called "results". This actually gives me a chance to easily flick over the sheets and compare the out come. Very handy in testing period. But the original idea was to have everything replaced on the origin sheet with the outcome. But I can later easily do this in the following way: your macro takes the information from sheet A, works its magic on it and produces an outcome on sheet B. I can then edit the code to clear contents of sheet A and copy contents of sheet B back to sheet A.

Cheers
Rain

Edit. oops. Double posting.

Hi watersev
I redid the instructions. But fundamentally the only thing that has changed from your post #9 attachment is that 2 columns namely E and H have been removed/deleted. This is from the starting setup. Because these columns have been removed from the start up setup, your code is now just looking to wrong places. All you need to to do is re-align the code so its looking the right columns. Regardless I re-did new instructions. I hope you will understand them as I'm not very good creating very clear instructions. Please note there are 4 sheets within this workbook and each one of them includes comments.


I'm very thankful of your support. I hope one day I can help some other people myself. There is so much to learn but I'm sure I will get there eventually.


Cheers

Rain

Hi watrsev. I tried the code with real data and encountered a bug. Please look the attached xls. On sheet "results" I have highlighted x3 rows in (rows 2666-2666). Now these rows have been combined/concatenated together while they shouldn't been. These three problem lines have the following Unique ID values
181024317810
181025891310
181026707810
If in sheet "2011" you use filter -> custom->equals to on column C, and use the unique ID 181024317810 you can see the following

-------col C ------------------------------------col F----------------------------------------------Col G
181024317810------------RN-207573, RN-207582------------------------------------------10
181024317810------------RN-207582----------------------------------------------------------5

So we know it should keep the top line and delete the lower line
But on result screen the macro will combine them together like this

-------col C ------------------------------------col F----------------------------------------------Col G
181024317810------------RN-207582, RN-207573, RN-207582------------------------15
And I don't understand why as it was working before.

Can you have a look and see whats wrong. Currently the macro bears name convert in module 4

Hi Watersev
I hope you can help me to make an adjustment or add feature to current code. I didn't realize that it can be important to capture one aspect of data until very recently when we were in bit of dispute with one of our suppliers. Capturing additional data could benefited us as we would known that we had caused a mistake ourselves when booking in some parts with wrong quantities. So it wasn't our supplier mistake as we wrongly accused them. So this is the feature I would like to add to the existing code.

I want to keep everything exactly the same way as it is at the moment (it works without any errors) but I would like it to capture the quantities information.

So every time the code takes the RN numbers and places them together on one cell on the same line I still want it to do the same thing but include the quantity information from column I.
The current code changes this:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207209----------------------------------------------------------1
181026699410------------RN-207210----------------------------------------------------------1
181026699410------------RN-207211----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1


to this:
-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207209, RN-207210, RN-207211------------------------3
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1

Now I would like code capture more information and change it to this instead the example above


-------col C ------------------------------------col G--------------------------------------------------------------------------------------------Col I
181026716910------------RN-207380--------------------------------------------------------------------------------------------------------1
181026688710------------RN-207090--------------------------------------------------------------------------------------------------------1
181026688810------------RN-207343--------------------------------------------------------------------------------------------------------1
181026699410------------RN-207209 - QTY 1, RN-207210 - QTY 1, RN-207211 - QTY 1--------------------------------------3
181026713110------------RN-207418--------------------------------------------------------------------------------------------------------1
181026712910------------RN-207412--------------------------------------------------------------------------------------------------------1

So the only amendments would be the ones in Green. Everything else should stay exactly as per current code as its working very efficiently and without any bugs.
I have attached spreadsheet that contains the current code and example sheet called "After". There are 2 example rows there. One is on row 199 the other one is row 200. Macro is s "convert short" which is in module 4.

I'm not sure how easy or hard it will be to add this feature in. I didn't even try to do myself as I'm very scared to destroy this bug free code.
I would be very thankful if you can have a look of the code.


Cheers
Rain

Hi watersev
Please ignore the comment in in cell F211. I didn't know how to explain it very well and I think I made it more confusing. But what it means is that all the the cells that normally are kept untouched as per current code should also be kept untouched with new ammended code.

Let me give an example.
I would like the code change this:
-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207209----------------------------------------------------------1
181026699410------------RN-207210----------------------------------------------------------1
181026699410------------RN-207211----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1

into this:

-------col C ------------------------------------col G--------------------------------------------------------------------------------------------Col I
181026716910------------RN-207380--------------------------------------------------------------------------------------------------------1
181026688710------------RN-207090--------------------------------------------------------------------------------------------------------1
181026688810------------RN-207343--------------------------------------------------------------------------------------------------------1
181026699410------------RN-207209 - QTY 1, RN-207210 - QTY 1, RN-207211 - QTY 1--------------------------------------3
181026713110------------RN-207418--------------------------------------------------------------------------------------------------------1
181026712910------------RN-207412--------------------------------------------------------------------------------------------------------1

but I wouldn't want the code change it into this: (see the the purpled lines. This is what i don't want it to do)

-------col C ------------------------------------col G--------------------------------------------------------------------------------------------Col I
181026716910------------RN-207380 - QTY 1 --------------------------------------------------------------------------------------------------------1
181026688710------------RN-207090 - QTY 1 --------------------------------------------------------------------------------------------------------1
181026688810------------RN-207343 - QTY 1 --------------------------------------------------------------------------------------------------------1
181026699410------------RN-207209 - QTY 1, RN-207210 - QTY 1, RN-207211 - QTY 1--------------------------------------------------3
181026713110------------RN-207418 - QTY 1 --------------------------------------------------------------------------------------------------------1
181026712910------------RN-207412 - QTY 1 --------------------------------------------------------------------------------------------------------1

So another words. It only needs to add this quantity information for the lines where the old code was adding the RN numbers together. I hope this is a better explanation. I quite didn't know how to explain. If the amended code will return exactly the same result as on sheet "after" then it should be correct. Thank you for looking into this.

Cheers
Rain

New feature works brilliantly but now there is a small bug. Let me demonstrate.

On sheet "2011", data is like this:

-------col C ------------------------------------col G--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Col I
181026437410 ------------ RN-211621-1, RN-211622-1, RN-211623-1, RN-211626-1, RN-211627-1, RN-211628-1, RN-211629-1 --------------------------------------------------------------------------------------------------------------- 7
181026437410 ------------ RN-211621 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1
181026437410 ------------ RN-211622 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1
181026437410 ------------ RN-211623 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1
181026437410 ------------ RN-211626 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1
181026437410 ------------ RN-211627 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1
181026437410 ------------ RN-211628 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1
181026437410 ------------ RN-211629 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1


With current macro on after running it it returns the following on sheet "results":
-------col C ------------------------------------col G-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Col I
181026437410 ------------ RN-211621-1, RN-211622-1, RN-211623-1, RN-211626-1, RN-211627-1, RN-211628-1, RN-211629-1, RN-211621-1, RN-211622-1, RN-211623-1, RN-211626-1, RN-211627-1, RN-211628-1, RN-211629-1 -- 14


While it should look like this:

-------col C ------------------------------------col G--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Col I
181026437410 ------------ RN-211621-1, RN-211622-1, RN-211623-1, RN-211626-1, RN-211627-1, RN-211628-1, RN-211629-1 --------------------------------------------------------------------------------------------------------------- 7

So in effect the current code adds them to this PO 181026437410 but it shouldn't as it already contains those RN numbers.

New spreadsheet attached contains this example.

Can you have a look oh it when you have some free time?


Many thanks
Rain