How To Create A Unique List From Multiple Sheets Using Excel Formula

[RDaga]

Hi Friends, I needed some help with Excel. Is there a way we can generate a list of unique values in one sheet from different sheets. Also is it possible that when a new sheet is added it automatically adds the new sheet data and update the list. I have attached a sample sheet.

It will be a big help as I have been stuck on this for some time and really needed some help with this

 

[Belleke]

Try this for a starter, according to your example.

Sub Belle()
    Dim s1 As Worksheet, ws As Worksheet
    Set s1 = Sheets("Sheet1")
    Dim lr As Long
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then
            lr = s1.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("A2").CurrentRegion.Copy
            s1.Range("A" & lr + 1).PasteSpecial xlPasteValues
        End If
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Complete"
End Sub

 

[RDaga]

Thank you Belleke for the quick help. This is amazing and using it I identified that my initial file was not complete. Sorry for that. Hence, I have updated the file.
Master Report - will have a report from the sheet Data Validation
Data Validation - when we run the macro. The macro will check Column A for all the sheets right (28-11-23, 21-11-23, 14-11-23 and this will keep on growing every week) to this and create a list of unique values of data in column A in these sheets

 

[AlanSidman]

Are you wanting the items totaled. You have not shown us the expected results so it is difficult to know exactly your needs.

 

[AlanSidman]

Saw your example and here is a power query solution

l

et
    T1 = Excel.CurrentWorkbook(){[Name="_111423"]}[Content],
    T1.1 = Table.AddColumn(T1, "TableName", each 111423),
    T2 = Excel.CurrentWorkbook(){[Name="_112123"]}[Content],
    T2.1 = Table.AddColumn(T2, "TableName", each 112123),
    T3 = Excel.CurrentWorkbook(){[Name="_112823"]}[Content],
    T3.1 = Table.AddColumn(T3, "TableName", each 112823),
    TA= Table.Combine({T1.1,T2.1,T3.1}),
    #"Grouped Rows" = Table.Group(TA, {"TableName", "Column1"}, {{"Sum", each List.Sum([Column2]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"TableName", "Date"}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type text}}, "en-US")[Date]), "Date", "Sum")
 in   
    #"Pivoted Column"

 

[RDaga]

Thank you Alan but I am afraid I am not skilled with power query

 

[AlanSidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

FYI: I just notice the first word in my MCode is not et but should be let if you copy it.

 

[herofox]

Try by this

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("</b><b>",1,'28-11-23 :14-11-23'!$A$1:$A$50),"+","</b><b>")&"</b></a>","//b[not(preceding::*=.)]["&ROW(A1)&"]"),"")

 

[RDaga]

Herofox, thank you this is amazing. I tried the formula but it looks to have some limitations as the moment i cross 677 rows it is not returning any value
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("</b><b>",1,'28-11-23 :14-11-23'!$A$2:$A$677),"+","</b><b>")&"</b></a>","//b[not(preceding::*=.)]["&ROW(A1)&"]"),"")

 

[RDaga]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

FYI: I just notice the first word in my MCode is not et but should be let if you copy it.

Sure Alan will go through these resource and will upskill my self

 

[Peter Bartholomew]

Excel dynamic array formulas are still playing catchup with parts of the Power Query functionality.
I obtained the following

using the formula

= LET(
    appendedData,    VSTACK(DataSource),
    attributeName,   TAKE(appendedData,,1),
    attributeValues, TAKE(appendedData,,-1),
    distinct,        UNIQUE(FILTER(attributeName, attributeName<>"")),
    sheetAttributes, DROP(WRAPCOLS(attributeName, 32),,1),
    sheetValues,     DROP(WRAPCOLS(attributeValues, 32),,1),
    sheetIndices,    SEQUENCE(1, COLUMNS(sheetAttributes)),
    REDUCE(distinct, sheetIndices,
      LAMBDA(acc,k,
        HSTACK(acc, XLOOKUP(distinct, CHOOSECOLS(sheetAttributes, k), CHOOSECOLS(sheetValues, k), ""))
      )
    )
  )

applied to the 3D data range,

 

[herofox]

RDaga ...No Problem ,You Can Increase this Range 677 To as You Want Even Till 100000​

 

[RDaga]

Hi Herofox, the moment i increase the number from 677 it does not return the actual value.

RDaga ...No Problem ,You Can Increase this Range 677 To as You Want Even Till 100000​

 

[herofox]

This issue has become larger than it is in size. As you can see, the problem is yours, as the file is working efficiently