array formula

G

gakiss2

New Member
Joined
Dec 5, 2018
Messages
17
I have used array formulas successfully but only when the result is a single value. It was my impression that excel could list out the results when there are multiple resuls. I don't see where I can attach an excel file. =IFERROR(INDEX('All GK Data'!I:I, MATCH(0,COUNTIF($M$19:M19, 'All GK Data'!I:I), 0)),"")
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
update. I had some success with getting the results to cascade but I'm looking for unique distinct but got all the results exactly the same. Also tried a different formula I got from a google search: =LOOKUP(2, 1/((COUNTIF($L$19:L19, 'All GK Data'!I:I)=0)*('All GK Data'!I:I<>"")), 'All GK Data'!I:I)
 
Upvote 0
the results would be a list of the items from the category column which contain only one instance of each category. Later will will use sumifs to addup the total reject qty for each of these categories.

[TABLE="width: 468"]
<colgroup><col width="64" style="width: 48pt;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2872;"> <col width="70" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2474;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2787;"> <col width="64" style="width: 48pt;" span="2"> <col width="64" style="width: 48pt;" span="2"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"> <tbody>[TR]
[TD="width: 64"]IDX[/TD]
[TD="width: 81"]Supplier[/TD]
[TD="width: 70"]RejNum[/TD]
[TD="width: 78"]DateIssu[/TD]
[TD="width: 64"]QtyRej[/TD]
[TD="width: 64"]Ship[/TD]
[TD="width: 64"]Item[/TD]
[TD="width: 64"]Descr[/TD]
[TD="width: 75"]Category[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Bobalee[/TD]
[TD="align: right"]109016[/TD]
[TD="align: right"]7/3/2018[/TD]
[TD="width: 64"]1[/TD]
[TD="width: 64"][/TD]
[TD]S2J1014[/TD]
[TD]CYL CAB TILT[/TD]
[TD]Function[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]Bobalee[/TD]
[TD="bgcolor: transparent, align: right"]109180[/TD]
[TD="bgcolor: transparent, align: right"]7/10/2018[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]S3J0193[/TD]
[TD="bgcolor: transparent"]CYL ASSY BEAM TOP[/TD]
[TD="bgcolor: transparent"]Dimensional[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Bobalee[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]9/26/2018[/TD]
[TD="width: 64"]1[/TD]
[TD="width: 64"][/TD]
[TD]T2J0087[/TD]
[TD]CYL CAB TILT[/TD]
[TD]Missing[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]Bobalee[/TD]
[TD="bgcolor: transparent, align: right"]382[/TD]
[TD="bgcolor: transparent, align: right"]10/16/2018[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]S2J1014[/TD]
[TD="bgcolor: transparent"]CYL CAB TILT[/TD]
[TD="bgcolor: transparent"]Orifice[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][td="bgcolor: #DCE6F1"]
K​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
IDX​
[/td][td]
Supplier​
[/td][td]
RejNum​
[/td][td]
DateIssu​
[/td][td]
QtyRej​
[/td][td]
Ship​
[/td][td]
Item​
[/td][td]
Descr​
[/td][td]
Category​
[/td][td][/td][td]
List​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
1​
[/td][td]
Bobalee​
[/td][td]
109016​
[/td][td]
07/03/2018​
[/td][td]
1​
[/td][td][/td][td]
S2J1014​
[/td][td]
CYL CAB TILT​
[/td][td]
Function​
[/td][td][/td][td]
Function​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
2​
[/td][td]
Bobalee​
[/td][td]
109180​
[/td][td]
07/10/2018​
[/td][td]
1​
[/td][td][/td][td]
S3J0193​
[/td][td]
CYL ASSY BEAM TOP​
[/td][td]
Dimensional​
[/td][td][/td][td]
Dimensional​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
3​
[/td][td]
Bobalee​
[/td][td]
179​
[/td][td]
09/26/2018​
[/td][td]
1​
[/td][td][/td][td]
T2J0087​
[/td][td]
CYL CAB TILT​
[/td][td]
Missing​
[/td][td][/td][td]
Missing​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
4​
[/td][td]
Bobalee​
[/td][td]
382​
[/td][td]
10/16/2018​
[/td][td]
1​
[/td][td][/td][td]
S2J1014​
[/td][td]
CYL CAB TILT​
[/td][td]
Orifice​
[/td][td][/td][td]
Orifice​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
5​
[/td][td]
Bobalee​
[/td][td]
111​
[/td][td]
10/18/2018​
[/td][td]
1​
[/td][td][/td][td]
S2J1014​
[/td][td]
CYL CAB TILT​
[/td][td]
Function​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
6​
[/td][td]
Bobalee​
[/td][td]
222​
[/td][td]
11/02/2018​
[/td][td]
2​
[/td][td][/td][td]
S3J0193​
[/td][td]
CYL ASSY BEAM TOP​
[/td][td]
Dimensional​
[/td][td][/td][td][/td][/tr]
[/table]

Array formula in K2 copied down
=IFERROR(INDEX($I$2:$I$7,MATCH(0,COUNTIF(K$1:K1,I$2:I$7),0)),"")
Ctrl+Shift+Enter

M.
 
Upvote 0
Marcelo Branco said:
Something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
A​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
B​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
C​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
D​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
E​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
F​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
G​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
H​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
I​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
J​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
K​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
1​
[/TD]
[TD]
IDX​
[/TD]
[TD]
Supplier​
[/TD]
[TD]
RejNum​
[/TD]
[TD]
DateIssu​
[/TD]
[TD]
QtyRej​
[/TD]
[TD]
Ship​
[/TD]
[TD]
Item​
[/TD]
[TD]
Descr​
[/TD]
[TD]
Category​
[/TD]
[TD][/TD]
[TD]
List​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
Bobalee​
[/TD]
[TD]
109016​
[/TD]
[TD]
07/03/2018​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
S2J1014​
[/TD]
[TD]
CYL CAB TILT​
[/TD]
[TD]
Function​
[/TD]
[TD][/TD]
[TD]
Function​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
Bobalee​
[/TD]
[TD]
109180​
[/TD]
[TD]
07/10/2018​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
S3J0193​
[/TD]
[TD]
CYL ASSY BEAM TOP​
[/TD]
[TD]
Dimensional​
[/TD]
[TD][/TD]
[TD]
Dimensional​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
4​
[/TD]
[TD]
3​
[/TD]
[TD]
Bobalee​
[/TD]
[TD]
179​
[/TD]
[TD]
09/26/2018​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
T2J0087​
[/TD]
[TD]
CYL CAB TILT​
[/TD]
[TD]
Missing​
[/TD]
[TD][/TD]
[TD]
Missing​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
5​
[/TD]
[TD]
4​
[/TD]
[TD]
Bobalee​
[/TD]
[TD]
382​
[/TD]
[TD]
10/16/2018​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
S2J1014​
[/TD]
[TD]
CYL CAB TILT​
[/TD]
[TD]
Orifice​
[/TD]
[TD][/TD]
[TD]
Orifice​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
6​
[/TD]
[TD]
5​
[/TD]
[TD]
Bobalee​
[/TD]
[TD]
111​
[/TD]
[TD]
10/18/2018​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
S2J1014​
[/TD]
[TD]
CYL CAB TILT​
[/TD]
[TD]
Function​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
7​
[/TD]
[TD]
6​
[/TD]
[TD]
Bobalee​
[/TD]
[TD]
222​
[/TD]
[TD]
11/02/2018​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
S3J0193​
[/TD]
[TD]
CYL ASSY BEAM TOP​
[/TD]
[TD]
Dimensional​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in K2 copied down
=IFERROR(INDEX($I$2:$I$7,MATCH(0,COUNTIF(K$1:K1,I$2:I$7),0)),"")
Ctrl+Shift+Enter

M.
Click to expand...

Thank You. still having trouble. almost works but still just repeats the first category item over and over. Also range is on another tab. I don't think that matters, seems to be pulling the right data but thought I would mention it just in case.
 
Upvote 0
gakiss2 said:
Thank You. still having trouble. almost works but still just repeats the first category item over and over. Also range is on another tab. I don't think that matters, seems to be pulling the right data but thought I would mention it just in case.
Click to expand...

And when I put it in I forgot to change K$1:K1 to the correc address but I worked, albeit not correctly, repeat s first category over and over. Sorr for the typos, this site is actinweird andnotaknkstrok correctly.
 
Upvote 0
gakiss2 said:
And when I put it in I forgot to change K$1:K1 to the correc address but I worked, albeit not correctly, repeat s first category over and over. Sorr for the typos, this site is actinweird andnotaknkstrok correctly.
Click to expand...

I tried it on the same tab but the same result, Function, function,....
 
Upvote 0
I do not know why the formula is not working for you. This is a well-known and tested formula.
One last question
Did you confirm the formula with Ctrl+Shift+Enter simultaneously?



M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA INDEX and MATCH with 2 different tables
Replies
2
Views
121
_Chanty_
C
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
548
felixstraube
F
L
  • Solved
Index and match function spanning multiple columns and rows
Replies
2
Views
336
L8NightKebab
L
S
  • Question Question
Pivot Chart Question
Replies
1
Views
212
Sergius
S
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

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