Collapse duplicates and only keep those values for which the values align

[noname91]

Hello all,

I'm trying to achieve something fairly straightforward, but might be a bit tricky to explain. I've been using multiple table copies and vlookup to do this, but I'm hoping there is a faster way to go about it using a macro.

I will explain with a simple example:

I have a group of duplicate numbers in column A. In column B, I have names of fruits (three only for this simple example). Columns C to E correspond to whether a row has a specific fruit (called Has_Apple, Has_Mango and Has_Cherry respectively), and contains a value of 1 corresponding to the column that refers to the row having that fruit. So if a row in column B contains the string Apple, Has_Apple for that row will be 1, while the other two columns will be 0.

What is needed is to determine if a group/collection of duplicate numbers has 1s occurring for all three columns, and if so, collapse the group of duplicates. The value of the fruit in column B isn't important, but you can imagine it containing the names of all three fruits. The columns C to E will predictably contain all 1s only for that collapsed row representing the entire collection. If, however, 1s are NOT occurring for ALL THREE columns, then the entire group of duplicates should be discarded.

As I mentioned, I was using multiple tables and vlookups for this before, but I'm not sure how this is going to be doable by macro. I've attached the screenshots with the original dataset, and a results set which shows what the result of such a macro would look like. Version of Excel is 2016, from Microsoft Office Professional Plus 2016.

Thanks for any help!

 

[Legacy 143009]

Hi,

I think it is doable and in a fast way! But I need a clue for how to differentiate the values in column B are the actual fruit names you are looking for.
I mean how would I know if you are looking for an Apple and not for an x?

 

[noname91]

Thanks for the reply!

The only way to differentiate I think would be if at least one of the three columns for that row contains a 1. Other than that, not too sure myself.

Also, I've attached a link to the example file if it helps:

Sample File

 

[Legacy 143009]

The only way to differentiate I think would be if at least one of the three columns for that row contains a 1.

I think I got what you mean. Otherwise, that fruit won't be considerable already. I think it's OK.

The link goes to some weird folder. I was not able to locate the file itself.

 

[Legacy 143009]

I think it is a Power BI file. I've never worked before.

Could you please share as a normal.xlsx file?

 

[Fluff]

Version of Excel is 2016, from Microsoft Office Professional Plus 2016.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[noname91]

I think it is a power BI file. I've never worked before.

Could you please share as a normal.xlsx file?

Sorry, copied wrong link. Here is the correct link:

Sample FIle

@Fluff - got it, doing now.

 

[Legacy 143009]

One last question:
Is such scenario possible?

Let say, should we look for a "1" for Apple always under the first column?

 

[noname91]

In that example you've posted, if Fruit under column B is Apple, only Has_Apple will be 1, while the other two will always be 0, so the scenario above will never happen. If a fruit is in column B, the 1 will only be for that column that corresponds to the fruit's name in column B while both other columns will be 0 since that is how they are being set in the actual data set.

Thanks!

 

[Legacy 143009]

I think it is beautiful

Sub test()
  Dim myArray As Variant, tempArray As Variant
  Dim fruits As Object, groups As Object, myRange As Range
  Dim i As Long, j As Long, fruitCount As Long
  Set myRange = Worksheets("Sheet1").Range("A1").CurrentRegion
  Set fruits = CreateObject("Scripting.Dictionary")
  Set groups = CreateObject("Scripting.Dictionary")
  myArray = myRange
  ReDim tempArray(1 To UBound(myArray, 2), 1 To 1)

  'First some setup
  For i = 2 To UBound(myArray, 1)
    If Not groups.Exists(myArray(i, 1)) Then
      groups.Add myArray(i, 1), 0
    End If
    For j = 3 To UBound(myArray, 2)
      If myArray(i, j) = 1 Then
        If Not fruits.Exists(myArray(i, 2)) Then
          fruits.Add myArray(i, 2), 0
        End If
        Exit For
      End If
 
    Next
  Next
  For i = 1 To UBound(myArray, 2)
    tempArray(i, 1) = myArray(1, i)
  Next
 
  'Make the checks
  For Each Group In groups
    For Each fruit In fruits
      For i = 2 To UBound(myArray, 1)
        If myArray(i, 1) = Group And myArray(i, 2) = fruit Then
          For j = 3 To UBound(myArray, 2)
            If myArray(i, j) = 1 Then
              fruitCount = fruitCount + 1
            End If
          Next
        End If
      Next
    Next
    'Make the final table
    If fruitCount = fruits.Count Then
      ReDim Preserve tempArray(1 To UBound(tempArray, 1), 1 To UBound(tempArray, 2) + 1)
      tempArray(1, UBound(tempArray, 2)) = Group
      tempArray(2, UBound(tempArray, 2)) = "All fruits exist"
      For i = 3 To UBound(tempArray, 1)
        tempArray(i, UBound(tempArray, 2)) = 1
      Next
    End If
    fruitCount = 0
  Next

  'Define some target in order to write the table
  Worksheets("Result").Range("A1").Resize(UBound(tempArray, 2), UBound(tempArray, 1)).Value = Application.Transpose(tempArray)
End Sub