# Macro is required to the data filter



## harinsh (Dec 25, 2022)

Hi Excel Experts, 

I am looking for one small automation macro to filter the data below and need to keep one set of data from the input to the output. 

We have three columns below and Col-1 where "Y" is applicable in Col-3 then Col-1 cell value should be base and corresponding Col-2 data should copy against the value. Each corresponding value should paste in the different table (refer the output). 

Table 1- Input

Col-1Col-2Col-31000110011Y10011100121000310013Y10012100141000510015Y10014100161000710017Y10016100181000910019Y1001810020


Table 2- Output

OutputCol-1Col-210001100011000110011100011001210001100141000510015100051001610005100181000510020

Let me know if you need further clarification. 

Thank you,


----------



## Peter_SSs (Dec 25, 2022)

harinsh said:


> Let me know if you need further clarification.


I don't see how you got the Output table from that Input. Can you explain step-by-step how you got each row in the Output?


BTW, 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’)


----------



## harinsh (Dec 25, 2022)

Here is the step by step

1- First filter "Y" in col-3 and take the col-1 cell value (i.e 10001)
2- Paste the above cell value in col-2 and take the value (10011)
3- Paste both above values in empty cells one beside one (10001   - 10011  - Y )

The filter should continue until getting the value incase no value filter should stop and proceed to the second set of data based on value "Y" in Col-3.


----------



## Peter_SSs (Dec 25, 2022)

Perhaps I am missing something obvious, but I cannot see it.


----------



## harinsh (Dec 25, 2022)

Here is the step by step

1- First filter "Y" in col-3 and take the col-1 cell value (i.e 10001)
2- Paste the above cell value in col-2 and take the value (10011)
3- Paste both above values in empty cells one beside one (10001 - 10011 - Y )

The filter should continue until getting the value in case no value filter should stop and proceed to the second set of data based on value "Y" in Col-3.

Do you need more clarifications?


----------



## Peter_SSs (Dec 25, 2022)

That is the same description that you gave before. It makes no sense to me.
Below I have filtered "Y" in col 3.
Why are there *four *"10001" values in column 1 of the Output?
Where do 10012 and 10014 come from in cells B20 and B21? They seem to have nothing to do with either 10001 in the Input and they also have nothing to do with Y in the Input.

In the Input, the second Y value is for 10003 and 10013. Neither of those values appear in your Output. I don't understand that.

Where do 10016, 10018 and 10020 in cells B23:B26? They seem to have nothing to do with either 10005 in the Input and they also have nothing to do with Y in the Input.

In the Input, the fourth Y value is for 10007 and 10017. Neither of those values appear in your Output. I don't understand that.

In the Input, the fifth Y value is for 10009 and 10019. Neither of those values appear in your Output. I don't understand that.

harnish.xlsmABC1Table 1- Input2Col-1Col-2Col-331000110011Y51000310013Y71000510015Y91000710017Y111000910019Y131415Table 2- Output16Output17Col-1Col-218100011000119100011001120100011001221100011001422231000510015241000510016251000510018261000510020Sheet1


----------



## harinsh (Dec 25, 2022)

Need to filter the col-3 value "Y" and take the col-1 value and taking the col-2 value and keeping the values one beside another and take the col-2 value again filtering in col-1 and past beside to the next value.


----------



## harinsh (Dec 25, 2022)

Below I have filtered "Y" in col 3.

Why are there *four *"10001" values in column 1 of the Output?
*Each "Y" value we call as one set and corresponding hierarchy need to be arranged one after another *

Where do 10012 and 10014 come from in cells B20 and B21? They seem to have nothing to do with either 10001 in the Input and they also have nothing to do with Y in the Input.
*if you filter 10011 then you get 10012 and if you filter 10012 you will get 10014 and so on..., Input "Y" only to take the set as explained above 



*

In the Input, the second Y value is for 10003 and 10013. Neither of those values appear in your Output. I don't understand that.
*Each "Y" value we call as one set and corresponding hierarchy need to be arranged one after another. If you filter 10003 there is no value so this set end with below output 


*

Where do 10016, 10018 and 10020 in cells B23:B26? They seem to have nothing to do with either 10005 in the Input and they also have nothing to do with Y in the Input.
*Its hierachy need to align one after one 


*

In the Input, the fourth Y value is for 10007 and 10017. Neither of those values appear in your Output. I don't understand that.
*Same as above explained if not further data just make it as one set without hiearchy 


*

In the Input, the fifth Y value is for 10009 and 10019. Neither of those values appear in your Output. I don't understand that.
*Same as above explained if not further data just make it as one set without hiearchy 


*

Corrected output




My apologies if any confustion.


----------



## Peter_SSs (Dec 26, 2022)

OK, no wonder I couldn't understand the (incorrect) original sample and results! 
Now I understand.

Try this with a *copy* of your workbook.


```
Sub Hierarchy()
  Dim d As Object
  Dim a As Variant, b As Variant, Colm1 As Variant, Colm2 As Variant
  Dim i As Long, j As Long, k As Long
  
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("B2").End(xlDown).Offset(, 1)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  ReDim b(1 To Rows.Count, 1 To 2)
  For i = 1 To UBound(a)
    If a(i, 3) = "Y" Then
      Colm1 = a(i, 1)
      Colm2 = a(i, 2)
      If d.exists(Colm2) Then
        k = k + 1
        Do
          b(k, 1) = Colm1
          b(k, 2) = Colm2
          k = k + 1
          Colm2 = d(Colm2)
        Loop Until IsEmpty(Colm2)
      End If
    End If
  Next i
  If k > 0 Then
    With Range("A1").End(xlDown).Offset(4)
      .Resize(k, 2).Value = b
      .Offset(-1).Resize(, 2).Value = Range("A1:B1").Value
    End With
  End If
End Sub
```

Sheet before code:

harnish.xlsmABC1Col-1Col-2Col-321000110011Y3100111001241000310013Y5100121001461000510015Y7100151001681000710017Y91001610018101000910019Y111001810020121314151617181920Sheet1

After code:

harnish.xlsmABC1Col-1Col-2Col-321000110011Y3100111001241000310013Y5100121001461000510015Y7100151001681000710017Y91001610018101000910019Y111001810020121314Col-1Col-21510001100111610001100121710001100141819100051001520100051001621100051001822100051002023Sheet1


❗Could you also please address this point I made earlier?


Peter_SSs said:


> BTW, 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’)


----------



## harinsh (Dec 26, 2022)

Wonderfully, you have done what exactly I was looking for only one request, is it possible to add even no hierarchy values as well something like this as one set? So, it will be clear that this value does not have any hierarchy.


----------



## harinsh (Dec 25, 2022)

Hi Excel Experts, 

I am looking for one small automation macro to filter the data below and need to keep one set of data from the input to the output. 

We have three columns below and Col-1 where "Y" is applicable in Col-3 then Col-1 cell value should be base and corresponding Col-2 data should copy against the value. Each corresponding value should paste in the different table (refer the output). 

Table 1- Input

Col-1Col-2Col-31000110011Y10011100121000310013Y10012100141000510015Y10014100161000710017Y10016100181000910019Y1001810020


Table 2- Output

OutputCol-1Col-210001100011000110011100011001210001100141000510015100051001610005100181000510020

Let me know if you need further clarification. 

Thank you,


----------



## Peter_SSs (Dec 26, 2022)

harinsh said:


> , is it possible to add even no hierarchy values as well something like this as one set? So, it will be clear that this value does not have any hierarchy.


Sure, it just requires the removal of two lines of the previous code.


```
Sub Hierarchy_v2()
  Dim d As Object
  Dim a As Variant, b As Variant, Colm1 As Variant, Colm2 As Variant
  Dim i As Long, j As Long, k As Long
  
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("B2").End(xlDown).Offset(, 1)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  ReDim b(1 To Rows.Count, 1 To 2)
  For i = 1 To UBound(a)
    If a(i, 3) = "Y" Then
      Colm1 = a(i, 1)
      Colm2 = a(i, 2)
'      If d.exists(Colm2) Then
        k = k + 1
        Do
          b(k, 1) = Colm1
          b(k, 2) = Colm2
          k = k + 1
          Colm2 = d(Colm2)
        Loop Until IsEmpty(Colm2)
'      End If
    End If
  Next i
  If k > 0 Then
    With Range("A1").End(xlDown).Offset(4)
      .Resize(k, 2).Value = b
      .Offset(-1).Resize(, 2).Value = Range("A1:B1").Value
    End With
  End If
End Sub
```


----------



## harinsh (Dec 26, 2022)

It works very well only the last change I won't disturb anymore and your help highly appreciate and very helpful. 

I want to keep same col-1 value should also keep at first level as like below highlighted what to be included in the code, sorry this was last moment change.


----------



## Peter_SSs (Dec 26, 2022)

Do you want this added to the first code (post #9) or the second code (post #11)?


----------



## harinsh (Dec 26, 2022)

Peter_SSs said:


> Do you want this added to the first code (post #9) or the second code (post #11)?


The second time posted code is perfectly working after the removal of the if condition and this needs to be added to the same #11.


----------



## Peter_SSs (Dec 26, 2022)

harinsh said:


> this needs to be added to the same #11.


In that case it sounds like you want this result (green cells to be added), not the one you showed in post #12?

harnish.xlsmABC1Col-1Col-2Col-321000110011Y3100111001241000310013Y5100121001461000510015Y7100151001681000710017Y91001610018101000910019Y111001810020121314Col-1Col-215100011000116100011001117100011001218100011001419201000310003211000310013222310005100052410005100152510005100162610005100182710005100202829100071000730100071001731321000910009331000910019Sheet1 (2)


----------



## harinsh (Dec 26, 2022)

that's correct your output


----------



## Peter_SSs (Dec 26, 2022)

harinsh said:


> that's correct your output



It is important that *you *give correct input/output in future. Multiple times in this thread you have given incorrect examples. Helpers will soon tire of helping you if they waste their time trying to do what you have asked, only to learn that you have not asked for the correct thing. 


Also, I have already asked twice in this thread (now three times) for you to *update your account details to include what Excel version(s) you are using *as that can sometimes make a difference to the best way to do something.


----------



## Peter_SSs (Dec 26, 2022)

```
Sub Hierarchy_v3()
  Dim d As Object
  Dim a As Variant, b As Variant, Colm1 As Variant, Colm2 As Variant
  Dim i As Long, j As Long, k As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("B2").End(xlDown).Offset(, 1)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  ReDim b(1 To Rows.Count, 1 To 2)
  For i = 1 To UBound(a)
    If a(i, 3) = "Y" Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 1)
      Colm1 = a(i, 1)
      Colm2 = a(i, 2)
        k = k + 1
        Do
          b(k, 1) = Colm1
          b(k, 2) = Colm2
          k = k + 1
          Colm2 = d(Colm2)
        Loop Until IsEmpty(Colm2)
    End If
  Next i
  If k > 0 Then
    With Range("A1").End(xlDown).Offset(4)
      .Resize(k, 2).Value = b
      .Offset(-1).Resize(, 2).Value = Range("A1:B1").Value
    End With
  End If
End Sub
```


----------



## harinsh (Dec 26, 2022)

Thank you very much Peter_SSs! It is working exactly as expected.


----------



## Peter_SSs (Dec 27, 2022)

You're welcome. 
(Pity we didn't ever find out what version you are using )


----------

