Extract Unique Sub-Heading Values List Starting with Numbers

[Malhotra Rahul]

Hi,

Please find here below the link to download the Sample workbook, where i want to extract the unique values which are only starting with numbers in Sheet1 from Column A. Request to you please help me.

Thank you in advance for your help.

Regards

https://drive.google.com/file/d/1Vx9wvINi3RzExrULDDELGO6JZIANGJ2L/view?usp=sharing

 

[Fluff]

How about

Sub GetUnique()
   Dim cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each cl In Range("A2", Range("A" & Rows.count).End(xlUp)).SpecialCells(xlConstants)
         If IsNumeric(Split(cl.Value, "-")(0)) Then .Item(cl.Value) = Empty
      Next cl
      Range("G3").Resize(.count).Value = Application.Transpose(.keys)
   End With
End Sub

 

[Malhotra Rahul]

Thats great Sir It really works perfect. Sir Is it possible through the excel formulae. The reason is Sir after pasting the raw data the command would be required to run. Would be highly appreciated Sir.

 

[Fluff]

Formulae are not my strong suit, so cannot help, but hopefully another member will come along & supply one.

 

[Peter_SSs]

Another way

Sub Unique_List()
  Range("H2").Formula = "=isnumber(left(A2,1)+0)"
  Range("A1", Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("H1:H2"), CopyToRange:=Range("G1"), Unique:=True
  Range("H2").ClearContents
End Sub

 

[Malhotra Rahul]

Dear Sir, can you please help me with providing the formula. Please.

 

[Malhotra Rahul]

How about

Sub GetUnique()
   Dim cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each cl In Range("A2", Range("A" & Rows.count).End(xlUp)).SpecialCells(xlConstants)
         If IsNumeric(Split(cl.Value, "-")(0)) Then .Item(cl.Value) = Empty
      Next cl
      Range("G3").Resize(.count).Value = Application.Transpose(.keys)
   End With
End Sub

Dear Fluff Sir,

Can you please modify your script to get the output in Sheet2 from Cell A1 when run the script.

 

[Peter_SSs]

I had missed your request for formulas.

- If you are not using the same sheet each time, then surely running the code is no harder than inserting the formula and copying down.

- If you are using the same sheet each time and just putting new data into column A, then the code can be set to run automatically as follows.
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    If Range("A" & Rows.Count).End(xlUp).Row > 1 Then
      Sheets("Sheet2").Columns("A").ClearContents
      Range("Z2").Formula = "=isnumber(left(A2,1)+0)"
      Range("A1", Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Z1:Z2"), CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=True
      Range("Z2").ClearContents
    End If
  End If
End Sub

Every time new data is put into column A, the old column G list will be cleared and the new one created without you doing anything.


However, if you really do want a formula, try this in cell G2, copied down as far as you might ever need. If you think the column A data might ever extend beyond row 500 then you will need to increase that number in the formula. However, this is already a fairly resource-heavy formula and increasing that row number will make it more so.

=IFERROR(INDEX(A$2:A$500,AGGREGATE(15,6,(ROW(A$2:A$500)-ROW(A$2)+1)/ISNUMBER(LEFT(A$2:A$500,1)+0),ROWS(G$2:G2))),"")

 

[Malhotra Rahul]

Thank you Dear Sir Fluff and Peter_Ss for your awesome support. The provided solutions worked great. Thank you Sir.

 

[Peter_SSs]

Not sure which suggestion you have settled on but in any case: You're welcome.