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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

C
  • Solved
UNIQUE(FILTER(INDIRECT formula
Replies
5
Views
904
KRice
K
S
  • Solved
Help, merging data rows with unique values.
Replies
6
Views
398
alansidman
A
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
135
SunnyAlv
SunnyAlv
J
  • Question
Consolidate data from multiple sheets based on values selected in dependent list boxes
Replies
0
Views
208
jconda
J
N
  • Solved
ByRow & Lambda with let function issue
Replies
8
Views
628
Georgiboy
Georgiboy

Forum statistics

Threads
1,214,780
Messages
6,121,522
Members
449,037
Latest member
tmmotairi

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