If, iserror, index, row

[arossijr]

I have another formula that is sorta working... I have it in another Workbook and it works great.. All i changed was the Table and Header info and it works but is a little wonked...

=IF(ISERROR(INDEX(DRMRwdData[#All],SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData[Store '#])),ROW(1:1)),2)),"",INDEX(DRMRwdData[#All],SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData[Store '#])),ROW(1:1)),2))

An it is entered as an Array formula...
In the other workbook it works great!! But in this one... It is not taking the very first line it should be and it is grabbing a line 1 below the what it should be...

So it is should be grabbing 1,2,3,4,5 it is grabbing 2,3,4,5,6

It is practically a copy paste from the other book, actually it is... then i changed the tables and headers and the =$B$1 ...

Im going knucking crazy...
again please tell me were where i goofed...

 

[Aladin Akyurek]

re: IF, ISERROR, INDEX, ROW

Care to post the first 5 rows of DRMwdData including only the relevant headers along with the desired results for the posted data?

 

[arossijr]

Re: IF, ISERROR, INDEX, ROW

I just came into the Office, I will post some data...

Cell $B$1 = 251

Table = DRMRwdData
Headers = [Store #] [Account] [Account Name] [Qty] [Sell]
Data = 251 1253 Test 1 1 $2.50
251 1254 Test 2 2 $5.00
251 1256 Test 3 1 $2.50
251 1257 Test 4 4 $10.00
251 1258 Test 5 1 $2.50
252 2253 Test 6 1 $2.50
252 2254 Test 7 1 $2.50

 

[Aladin Akyurek]

Re: IF, ISERROR, INDEX, ROW

And you want to pull the data related to B1, housing a store # like 251, right?

 

[arossijr]

Re: IF, ISERROR, INDEX, ROW

And you want to pull the data related to B1, housing a store # like 251, right?

Aladin, that would be correct!
For some reason what i have skips line 1 and brings in line one of the next store... i tried adding -Min with +1 and it cleared the table...

 

[Aladin Akyurek]

Re: IF, ISERROR, INDEX, ROW

Aladin, that would be correct!
For some reason what i have skips line 1 and brings in line one of the next store... i tried adding -Min with +1 and it cleared the table...

Book1
	B	C	D	E	F	G
1	251	5
2	Store #	Account	Account Name	Qty	Sell	Idx
3	251	1253	Test 1	1	2.5	1
4	251	1254	Test 2	2	5	2
5	251	1256	Test 3	1	2.5	3
6	251	1257	Test 4	4	10	4
7	251	1258	Test 5	1	2.5	5
8
Sheet1

In C1 enter:

=COUNTIFS(DRMRwdData[Store '#],B1)

In B3 enter, copy across to F3, and copy down:

=IF($G3="","",INDEX(DRMRwdData,$G3,MATCH(B$2,DRMRwdData[#Headers],0)))

In G3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$2:G2)>$C$1,"",SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData)-ROW(INDEX(DRMRwdData,1,1))+1),ROWS($G$2:G2)))

 

[arossijr]

Re: IF, ISERROR, INDEX, ROW

	B	C	D	E	F	G
Store #	Account	Account Name	Qty	Sell	Idx
Test 1
Test 2
Test 3
Test 4
Test 5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]1253[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]1254[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]1256[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]1257[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]1258[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]

</tbody>

Sheet1​

In C1 enter:

=COUNTIFS(DRMRwdData[Store '#],B1)

In B3 enter, copy across to F3, and copy down:

=IF($G3="","",INDEX(DRMRwdData,$G3,MATCH(B$2,DRMRwdData[#Headers],0)))

In G3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$2:G2)>$C$1,"",SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData)-ROW(INDEX(DRMRwdData,1,1))+1),ROWS($G$2:G2)))

Aladin, Sorry I am confused... In my other workbook with the same formula it works.. The only difference is the Table and Header names and maybe the position on the sheet for the tables...
I dont mind using helper columns if needed... I just need to remember to hide them so that the End User doesnt see them and or i dont add them into a equation somewhere..
But i will try out you formulas and see what happens... Greatly appreciated!! If your bored I have another post for another formula that is sorta working... LOL
Thanks!
Tony

 

[Aladin Akyurek]

Re: IF, ISERROR, INDEX, ROW

Without the helper column...

In G3 control+shift+enter, not just enter, copy across to F3, and down:

=IF(ROWS($G$2:G2)>$C$1,"",INDEX(DRMRwdData,SMALL(IF(DRMRwdData[Store '#]=$B$1,ROW(DRMRwdData)-ROW(INDEX(DRMRwdData,1,1))+1),ROWS($G$2:G2)),MATCH(B$2,DRMRwdData[#Headers],0)))

 

[arossijr]

Re: IF, ISERROR, INDEX, ROW

Aladin,

I do truly appreciate the help, but with the other array formulas that in place adding the G Column in is going to cause a lot more harm. I know the formula i posted works, there is just something about the way it is working in the range. I just cannot narrow it down.
Thank you again for all your efforts, I am keeping the information you provided as reference and learning tools as well!!
Thank you,
Tony

 

[Aladin Akyurek]

Re: IF, ISERROR, INDEX, ROW

Aladin,

I do truly appreciate the help, but with the other array formulas that in place adding the G Column in is going to cause a lot more harm. I know the formula i posted works, there is just something about the way it is working in the range. I just cannot narrow it down.
Thank you again for all your efforts, I am keeping the information you provided as reference and learning tools as well!!
Thank you,
Tony

See: https://www.dropbox.com/s/1ujvwxkalw6t05g/arossijr sublist from a Table.xlsx?dl=0