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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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

H
  • Solved
VBA Code to Enter Formula in the Cell as Array Formula
Replies
5
Views
79
MARK858
MARK858
C
  • Question Question
VBA INDEX and MATCH with 2 different tables
Replies
2
Views
134
_Chanty_
C
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
681
felixstraube
F
L
  • Solved
Index and match function spanning multiple columns and rows
Replies
2
Views
438
L8NightKebab
L
J
  • Question Question
Help with Sumifs formula involving dates
Replies
5
Views
84
Joe4
Joe4

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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