Extract unique list from various sheets with combined unique and indirect function

M

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Hi,


could you please someone correct the below mentioned formula, as i am looking to extract a unique list from various sheets by using unique and indirect function and it is returns result as #VALUE!

=TRANSPOSE(SORT(UNIQUE(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!A4:A37"))))


Thanks for the help
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Thanks for the response,

herewith enclosed a sample in three different sheets for your reference.

Book1
A
4Attendance
5Attendance
6Covid19-Expences
7Demolition
8Dewatering
9Filling
10Earth works
11Formworks
12General Expense-indirect
13General Expense-indirect
14General Expense-indirect
15General Expense-indirect
16General Expense-indirect
17General Expense-indirect
18Ground Investigation
19Ground Investigation
20Ground Investigation
21Ground Investigation
22Ground Investigation
23Ground Investigation
24Ground Investigation
25General Expense-Site
26General Expense-Site
27General Expense-office
28General Expense-office
Sheet1


Book1
A
4site safety
5site safety
6site safety
7site safety
8site safety
9Road signage
10Road signage
11Road signage
12Road signage
13152645
14654152
15745485
16Shoring
17Shoring
18Shoring
19Shoring
20Shoring
21Shoring
Sheet2


Book1
A
4Attendance
5Attendance
6Covid19-Expences
7Demolition
8Dewatering
9Filling
10Earth works
11Formworks
12General Expense-indirect
13Road signage
14152645
15654152
16745485
17Shoring
18Shoring
19Ground Investigation
20Ground Investigation
21Ground Investigation
Sheet3
 
Upvote 0
With Power Query, bring each table into the PQ Editor. On the home tab, select Append queries. Append to new. Join the the three tables. In the newly created query, highlight the first column and select remove rows. Select remove duplicates. Close and Load to Excel.

Book21
A
1Column1
2Attendance
3Covid19-Expences
4Demolition
5Dewatering
6Filling
7Earth works
8Formworks
9General Expense-indirect
10Ground Investigation
11General Expense-Site
12General Expense-office
13site safety
14Road signage
15152645
16654152
17745485
18Shoring
Append1
 
Upvote 0
phew...
that was some task:

Here you go buddy file to download:

Minisheet:
Merging columns.xlsx
ABCDEFGHIJKLMNOPQRST
1Column to Merge info:
2How Many columns to Merge:3
3Table names to MergeTable1Table2Table3
4
5solution 1: 2 helper columnssolution 2: A bit of a mashupsolution 3: all in 1solution 4: using LETSolution 6: Power QuerySolution 6.5 with Table of origin
6Mash Up Helper tableTRUERowsColumnsMergedRowsColumnsMergedAll In 1All In 1Column1Column1Name
7Attendancesite safetyAttendance11Attendance11AttendanceAttendanceAttendanceAttendanceAttendanceTable1
8Attendancesite safetyAttendance21,04Covid19-Expences21,04Covid19-ExpencesCovid19-ExpencesCovid19-ExpencesCovid19-ExpencesCovid19-ExpencesTable1
9Covid19-Expencessite safetyCovid19-Expences31,08Demolition31,08DemolitionDemolitionDemolitionDemolitionDemolitionTable1
10Demolitionsite safetyDemolition41,12Dewatering41,12DewateringDewateringDewateringDewateringDewateringTable1
11Dewateringsite safetyDewatering51,16Filling51,16FillingFillingFillingFillingFillingTable1
12FillingRoad signageFilling61,2Earth works61,2Earth worksEarth worksEarth worksEarth worksEarth worksTable1
13Earth worksRoad signageEarth works71,24Formworks71,24FormworksFormworksFormworksFormworksFormworksTable1
14FormworksRoad signageFormworks81,28General Expense-indirect81,28General Expense-indirectGeneral Expense-indirectGeneral Expense-indirectGeneral Expense-indirectGeneral Expense-indirectTable1
15General Expense-indirectRoad signageGeneral Expense-indirect91,32Ground Investigation91,32Ground InvestigationGround InvestigationGround InvestigationGround InvestigationGround InvestigationTable1
16General Expense-indirect152645Road signage101,36General Expense-Site101,36General Expense-SiteGeneral Expense-SiteGeneral Expense-SiteGeneral Expense-SiteGeneral Expense-SiteTable1
17General Expense-indirect654152152645111,4General Expense-office111,4General Expense-officeGeneral Expense-officeGeneral Expense-officeGeneral Expense-officeGeneral Expense-officeTable1
18General Expense-indirect745485654152121,44site safety121,44site safetysite safetysite safetysite safetysite safetyTable2
19General Expense-indirectShoring745485131,48Road signage131,48Road signageRoad signageRoad signageRoad signageRoad signageTable2
20General Expense-indirectShoringShoring141,52152645141,52152645152645152645152645152645Table2
21Ground InvestigationShoringShoring151,56654152151,56654152654152654152654152654152Table2
22Ground InvestigationShoringGround Investigation161,6745485161,6745485745485745485745485745485Table2
23Ground InvestigationShoringGround Investigation171,64Shoring171,64ShoringShoringShoringShoringShoringTable2
24Ground InvestigationShoringGround Investigation181,68181,68
25Ground Investigation#N/A#N/A191,72191,72
26Ground Investigation#N/A#N/A201,76201,76
27Ground Investigation#N/A#N/A211,8211,8
28General Expense-Site#N/A#N/A221,84221,84
29General Expense-Site#N/A#N/A231,88231,88
30General Expense-office#N/A#N/A241,92241,92
31General Expense-office#N/A#N/A251,96251,96
321212
3322,0422,04
3432,0832,08
3542,1242,12
3652,1652,16
3762,262,2
3872,2472,24
3982,2882,28
4092,3292,32
41102,36102,36
42112,4112,4
43122,44122,44
44132,48132,48
45142,52142,52
46152,56152,56
47162,6162,6
48172,64172,64
49182,68182,68
50192,72192,72
51202,76202,76
52212,8212,8
53222,84222,84
54232,88232,88
55242,92242,92
56252,96252,96
571313
5823,0423,04
5933,0833,08
6043,1243,12
6153,1653,16
6263,263,2
6373,2473,24
6483,2883,28
6593,3293,32
66103,36103,36
67113,4113,4
68123,44123,44
69133,48133,48
70143,52143,52
71153,56153,56
72163,6163,6
73173,64173,64
74183,68183,68
75193,72193,72
76203,76203,76
77213,8213,8
78223,84223,84
79233,88233,88
80243,92243,92
81253,96253,96
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Sheet4
Cell Formulas
RangeFormula
D6D6=A7<>""
A7:C31A7=CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)
E7:E81E7=MOD(SEQUENCE(ROWS(A7#)*COLUMNS(A7#),,0),ROWS(A7#))+1
F7:F81F7=SEQUENCE(ROWS(A7#)*COLUMNS(A7#),,0,1/ROWS(A7#))+1
G7:G23G7=UNIQUE(FILTER(INDEX(A7#,E7#,F7#),NOT(ISNA(INDEX(A7#,E7#,F7#)))))
I7:I81I7=MOD(SEQUENCE(ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3))*COLUMNS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)),,0),ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)))+1
J7:J81J7=SEQUENCE(ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3))*COLUMNS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)),,0,1/ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)))+1
K7:K23K7=UNIQUE(FILTER(INDEX(A7#,E7#,F7#),NOT(ISNA(INDEX(A7#,E7#,F7#)))))
M7:M23M7=UNIQUE(FILTER( INDEX(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3),MOD(SEQUENCE(ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3))*COLUMNS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)),,0),ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)))+1,SEQUENCE(ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3))*COLUMNS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)),,0,1/ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)))+1), NOT(ISNA(INDEX(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3),MOD(SEQUENCE(ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3))*COLUMNS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)),,0),ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)))+1,SEQUENCE(ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3))*COLUMNS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)),,0,1/ROWS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)))+1)))))
O7:O23O7=UNIQUE( LET( Array, CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3), TotalRowsCount, SEQUENCE(ROWS(Array)*COLUMNS(CHOOSE(SEQUENCE(,C2),INDIRECT(C3),INDIRECT(D3),INDIRECT(E3),F3)),,0), RowsCount4Index, MOD(TotalRowsCount,ROWS(Array))+1, TotalColumnsCount, SEQUENCE(ROWS(Array)*COLUMNS(Array),,0,1/ROWS(Array))+1, TableResultWithNA, INDEX(Array,RowsCount4Index,TotalColumnsCount), FilteredResultTable, FILTER(TableResultWithNA,NOT(ISNA(TableResultWithNA))), FilteredResultTable))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O6Celldoes not contain a blank value textNO
O7:O84Celldoes not contain a blank value textNO
I6:K100Celldoes not contain a blank value textNO
E6:G100,M6Celldoes not contain a blank value textNO
A6:C6Expression=A7<>""textNO
A6:C100,P85:P100,M7:M84Celldoes not contain a blank value textNO
 
Upvote 0
Many thanks Radoslaw & all for your amazing solution,


could you please suggest a formula for extract a unique list without numeric values, required a list only text values.


Thanks,
 
Upvote 0
Thanks for the response,

In your enclosed sheet i did not find any solution to post#06, might be you send same sheet again which is shared in Post#05.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Averageif of cells from three sheets
Replies
7
Views
289
Fluff
Fluff
C
  • Solved
List Results from All Sheets Search
2
Replies
16
Views
212
MikeVol
MikeVol
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
350
RoryA
RoryA
O
  • Solved
SORTBY problems
2
Replies
12
Views
591
ollyhughes1982
O
C
  • Solved
UNIQUE(FILTER(INDIRECT formula
Replies
5
Views
2K
KRice
K

Forum statistics

Threads
1,223,705
Messages
6,173,991
Members
452,541
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top