If-And (?) Loop construction (?) to delete multiple rows based on multiple conditions?

[Lil2606]

Hi all,

I am struggling with this for a while... I have the following excel sheet as an example data set

Column A Column B Column C Column D
1 First names Last names Fruits Basket sizes
2 John Smith Raspberry 03 - Basket
3 John Smith Raspberry 05 - Basket
4 John Smith Raspberry 03 - Basket
5 John Smith Cherry Small Basket
6 John Smith Raspberry 03 - Basket
7 Jane Doe Raspberry 03 - Basket
8 Jane Doe Raspberry Small Basket
9 John Page Apple 03 - Basket
10 John Page Apple 03 - Basket
11 John Page Apple Small Basket
12 John Page Apple 05 - Basket
13 Grace Smith Raspberry 03 - Basket
14 Grace Smith Apple 05 - Basket
15 Grace Smith Cherry Small Basket

This is a table, so a listobject, and first row is header row (pretty obviously)
What I would like to do is write a VBA code to get rid of lines that are the same first and last name, and the same fruit, and only keep 1 line (remove duplicates/triplicates)
Either ignoring what is in Column D, or possibly, if there is an 05-Basket, that should be the line that is kept, if there is no "05*" it doesn't matter which line is kept. Also if it can't be done this way and lets say its always the first or last row of the group that is kept that is also good I will just sort it first. (Additional info, there will never be more than one 05 - Basket for the same name and fruit)
So the optimal outcome of this would be:

Column A Column B Column C Column D
1 First names Last names Fruits Basket sizes
2 John Smith Raspberry 05 - Basket
3 John Smith Cherry Small Basket
4 Jane Doe Raspberry 03 - Basket
5 John Page Apple 05 - Basket
6 Grace Smith Raspberry 03 - Basket
7 Grace Smith Apple 05 - Basket
8 Grace Smith Cherry Small Basket

I'm open to any creative solutions.. maybe a column could be inserted as "sorting column" in which the first names, last names and fruits are combined? And based on that column delete duplicates/triplicates?

If empty rows are left, that's also fine I will get rid of them after.

I really just need to figure out how to get rid of the unnecessary rows.. its weeks that I am trying to do this but I am quite new to VBA so its a bit above me yet.. hence if you have a solution, could you also give an explanation on how it does what it does please? I would like to learn.
Please help!
Thank you!

 

[Lil2606]

Super! Thank you very much!
Still don't understand how your code does what it does, I mean I get the sort part of it, but not the part how it deleted the duplicates..
Can you please explain it a little?

Cl is Column? Cell?
What is scripting.dictionary, and compare mode 1?
ValU= join (join what?)

 [COLOR=#333333]Set Rng = Cl.Resize(, 4) Else Set Rng = Union(Rng, Cl.Resize(, 4))[/COLOR]

in that (, 4) is column index? 4th column? If I have 20 is that supposed to change to 20?

 

[Fluff]

10       With CreateObject("scripting.dictionary")
11          .CompareMode = 1
12          For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
13             ValU = join(Application.Index(Cl.Resize(, 3).Value, 1, 0), "|")
14             If Not .Exists(ValU) Then
15                .Add ValU, Nothing
16             Else
17                If Rng Is Nothing Then Set Rng = Cl.Resize(, 4) Else Set Rng = Union(Rng, Cl.Resize(, 4))
18             End If
19          Next Cl
20       End With
21       If Not Rng Is Nothing Then Rng.Delete

10) creates a dictionary see here for more info https://excelmacromastery.com/vba-dictionary/
11) sets comparemode to 1 (not case sensitive)
12) Starts to loop through each cell in column A
13) Joins (concatenates) columns A:C for each row with a | between values & assigns that value to the variable ValU
14) Checks if the value already exists in the dictionary
15) if it doesn't then add it as the Key with the Item being empty
17 ) if the value does exist set the variable Rng to be the width of your table (in this case 4 columns so resize (,4)) If Rng already has a value then set rng to be the existing range & the new range
21) If there are rows to be deleted, delete them

HTH

 

[Lil2606]

Thank you Very much!! For all your help!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...y-lines-based-on-conditions-with-example.html

In future please supply links to any other site where you have asked the same question (see rule 13 here along with the explanation: Forum Rules)

 

[cnestg8r]

Sorry for the delay. I had out-patient surgery since my last post.

The macro approach is is fine. If you want to try a formula approach...

F3: {=INDEX(data,MATCH(0,COUNTIFS($F$2:$F2,first,$G$2:$G2,last,$H$2:$H2,fruits),0),COLUMN(A1))}

CSE, copy right and down

Note changed "Small" in data to "00"

 

[cnestg8r]

Book1
	F	G	H	I	J	K	L	M	N
1	WITH FORMULAS					EXPECTED
2	First	Last	Fruits	Size		First	Last	Fruits	Size
3	John	Smith	Rasberry	05		John	Smith	Rasberry	05
4	John	Page	Apple	05		John	Smith	Cherry	Small
5	Grace	Smith	Apple	05		Jane	Doe	Rasberry	03
6	Jane	Doe	Rasberry	03		John	Page	Apple	05
7	Grace	Smith	Rasberry	03		Grace	Smith	Rasberry	03
8	John	Smith	Cherry	00		Grace	Smith	Apple	05
9	Grace	Smith	Cherry	00		Grace	Smith	Cherry	Small
10
11	F3: {=INDEX(data,MATCH(0,COUNTIFS($F$2:$F2,first,$G$2:$G2,last,$H$2:$H2,fruits),0),COLUMN(A1))}
12
13	CSE, copy right and down
14
15	Note changed "Small" in data to "00"
Sheet1

Cell Formulas
Range	Formula
F11	="F3: "&FORMULATEXT(F3)
F3	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F2,first,$G$2:$G2,last,$H$2:$H2,fruits),0),COLUMN(A1))}
F4	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F3,first,$G$2:$G3,last,$H$2:$H3,fruits),0),COLUMN(A2))}
F5	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F4,first,$G$2:$G4,last,$H$2:$H4,fruits),0),COLUMN(A3))}
F6	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F5,first,$G$2:$G5,last,$H$2:$H5,fruits),0),COLUMN(A4))}
F7	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F6,first,$G$2:$G6,last,$H$2:$H6,fruits),0),COLUMN(A5))}
F8	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F7,first,$G$2:$G7,last,$H$2:$H7,fruits),0),COLUMN(A6))}
F9	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F8,first,$G$2:$G8,last,$H$2:$H8,fruits),0),COLUMN(A7))}
G3	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F2,first,$G$2:$G2,last,$H$2:$H2,fruits),0),COLUMN(B1))}
G4	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F3,first,$G$2:$G3,last,$H$2:$H3,fruits),0),COLUMN(B2))}
G5	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F4,first,$G$2:$G4,last,$H$2:$H4,fruits),0),COLUMN(B3))}
G6	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F5,first,$G$2:$G5,last,$H$2:$H5,fruits),0),COLUMN(B4))}
G7	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F6,first,$G$2:$G6,last,$H$2:$H6,fruits),0),COLUMN(B5))}
G8	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F7,first,$G$2:$G7,last,$H$2:$H7,fruits),0),COLUMN(B6))}
G9	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F8,first,$G$2:$G8,last,$H$2:$H8,fruits),0),COLUMN(B7))}
H3	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F2,first,$G$2:$G2,last,$H$2:$H2,fruits),0),COLUMN(C1))}
H4	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F3,first,$G$2:$G3,last,$H$2:$H3,fruits),0),COLUMN(C2))}
H5	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F4,first,$G$2:$G4,last,$H$2:$H4,fruits),0),COLUMN(C3))}
H6	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F5,first,$G$2:$G5,last,$H$2:$H5,fruits),0),COLUMN(C4))}
H7	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F6,first,$G$2:$G6,last,$H$2:$H6,fruits),0),COLUMN(C5))}
H8	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F7,first,$G$2:$G7,last,$H$2:$H7,fruits),0),COLUMN(C6))}
H9	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F8,first,$G$2:$G8,last,$H$2:$H8,fruits),0),COLUMN(C7))}
I3	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F2,first,$G$2:$G2,last,$H$2:$H2,fruits),0),COLUMN(D1))}
I4	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F3,first,$G$2:$G3,last,$H$2:$H3,fruits),0),COLUMN(D2))}
I5	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F4,first,$G$2:$G4,last,$H$2:$H4,fruits),0),COLUMN(D3))}
I6	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F5,first,$G$2:$G5,last,$H$2:$H5,fruits),0),COLUMN(D4))}
I7	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F6,first,$G$2:$G6,last,$H$2:$H6,fruits),0),COLUMN(D5))}
I8	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F7,first,$G$2:$G7,last,$H$2:$H7,fruits),0),COLUMN(D6))}
I9	{=INDEX(data,MATCH(0,COUNTIFS($F$2:$F8,first,$G$2:$G8,last,$H$2:$H8,fruits),0),COLUMN(D7))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
data	=Sheet1!$A$3:$D$16
first	=Sheet1!$A$3:$A$16
fruits	=Sheet1!$C$3:$C$16
last	=Sheet1!$B$3:$B$16

 

[Lil2606]

Oh wow. Thank you for this, but I'm trying to avoid user inputs, thats why I'm doing it with VBA. This converter sheet I'm working on is for my colleagues who use excel to edit text... so I wouldn't go as far as ctrl + shift +enter with them :D