• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

RDaga

New Member
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
 

Attachments

  • Book2.xlsx
    11.1 KB · Views: 6
Try this for a starter, according to your example.
Code:
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
 
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
 

Attachments

  • Book2.xlsx
    13.3 KB · Views: 6
Are you wanting the items totaled. You have not shown us the expected results so it is difficult to know exactly your needs.
 
Saw your example and here is a power query solution

l
Code:
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"
 

Attachments

  • Book2.xlsx
    24.5 KB · Views: 7
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.
 
Try by this
Code:
=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)&"]"),"")
 

Attachments

  • Book2.xlsb
    26.2 KB · Views: 4
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)&"]"),"")
 
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 :)
 
Excel dynamic array formulas are still playing catchup with parts of the Power Query functionality.
I obtained the following
1701385540787.png
using the formula
Code:
= 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,
 

Attachments

  • Book2.xlsx
    21.1 KB · Views: 2

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

 

Attachments

  • Book1.xlsx
    22 KB · Views: 9
This issue has become larger than it is in size. As you can see, the problem is yours, as the file is working efficiently
 

Attachments

  • Screenshot 2023-12-04 000348.png
    Screenshot 2023-12-04 000348.png
    54 KB · Views: 6
Back
Top