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)),"")
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Marcelo Branco
MrExcel MVP
- Joined
- Aug 23, 2010
- Messages
- 17,103
- Office Version
- 2021
- 2010
- Platform
- Windows
Initially we will try the formula without considering the new criterion (B1).
If it works, I'll try to add the new requirement.
Say you have this in All GK Data
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[TD="bgcolor: #DCE6F1"]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD]
[TD]
[TD]
[/TR]
</tbody>[/TABLE]
Other sheet
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As you have more than 40K rows i suggest a more efficient formula
L20
=IFERROR(INDEX('All GK Data'!$I$2:$I$44444,SMALL(IF(FREQUENCY(IF('All GK Data'!$I$2:$I$44444<>"",MATCH('All GK Data'!$I$2:$I$44444,'All GK Data'!$I$2:$I$44444,0)),ROW('All GK Data'!$I$2:$I$44444)-ROW('All GK Data'!$I$2)+1),ROW('All GK Data'!$I$2:$I$44444)-ROW('All GK Data'!$I$2)+1),ROWS(L$20:L20))),"")
Ctrl+Shift+Enter
Follow these steps
Copy the formula above (Ctrl+C)
Select L20
Put the cursor in the Formula Bar and paste (Ctrl+V)
With the cursor still in the Formula Bar keep pressed the keys Ctrl and Shift
Hit Enter and release Ctrl and Shift
If everything works fine Excel automatically wraps the formula with curly braces {=IFERROR(.......)}. Check.
Now all you have to do is to copy (drag) the formula down till the result is a blank cell
Try it.
M.
If it works, I'll try to add the new requirement.
Say you have this in All GK Data
[TABLE="class: grid"]
<tbody>[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][/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][/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][/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][/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][/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][/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][/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][/TR]
</tbody>[/TABLE]
Other sheet
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
L
[/TD][/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19
[/TD][TD]
List
[/TD][/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20
[/TD][TD]
Function
[/TD][/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21
[/TD][TD]
Dimensional
[/TD][/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22
[/TD][TD]
Missing
[/TD][/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23
[/TD][TD]
Orifice
[/TD][/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24
[/TD][TD][/TD]
[/TR]
</tbody>[/TABLE]
As you have more than 40K rows i suggest a more efficient formula
L20
=IFERROR(INDEX('All GK Data'!$I$2:$I$44444,SMALL(IF(FREQUENCY(IF('All GK Data'!$I$2:$I$44444<>"",MATCH('All GK Data'!$I$2:$I$44444,'All GK Data'!$I$2:$I$44444,0)),ROW('All GK Data'!$I$2:$I$44444)-ROW('All GK Data'!$I$2)+1),ROW('All GK Data'!$I$2:$I$44444)-ROW('All GK Data'!$I$2)+1),ROWS(L$20:L20))),"")
Ctrl+Shift+Enter
Follow these steps
Copy the formula above (Ctrl+C)
Select L20
Put the cursor in the Formula Bar and paste (Ctrl+V)
With the cursor still in the Formula Bar keep pressed the keys Ctrl and Shift
Hit Enter and release Ctrl and Shift
If everything works fine Excel automatically wraps the formula with curly braces {=IFERROR(.......)}. Check.
Now all you have to do is to copy (drag) the formula down till the result is a blank cell
Try it.
M.
Last edited:
Upvote
0
Excel Facts
Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Excellent!. I don't have 40K entries now but I plan to add the data over time so in a few years I could potentially get to at least a few thousand. By the time I get to 44,000 we will all have computers installed and integrated to our brains at birth so we won't need my silly spreadsheets. I wil try to add the logic for the supplier and date but am certainly grateful for any addtl help. BTW Any idea why it is maddening to type on this forum. You have to type twice on some letters, some don't take and you have to go back over and correct so much. It drives me nuts.
Upvote
0
Marcelo Branco
MrExcel MVP
- Joined
- Aug 23, 2010
- Messages
- 17,103
- Office Version
- 2021
- 2010
- Platform
- Windows
Good news! The formula worked
I have no idea what you're complaining about - typing the same letter multiple times ...
M.
I have no idea what you're complaining about - typing the same letter multiple times ...
M.
Upvote
0
Must be on my end. It just happens with this forum so I thought maybe you had heard about it before. Not a big deal at all. Thanks Again. My thoughts on the rest of it is to use formulas to create another table which sorts off of those inputs I talked about and then re-wire the line you made for me to refer to the new filtered table that I make. Its definitely not 'clean' and probably not efficient but its all I have come up with so far. I couldn't find the place in the line you made where I could insert the logic to select data based on the inputs. I've never seen anything like that code before in my life and I don't pretend to understand it.Good news! The formula worked
I have no idea what you're complaining about - typing the same letter multiple times ...
M.
Upvote
0
