Formula of unique column values partially working

[inddon]

Hello There,

I have 2 tables as below:

-Table 1 (Column: Tab1Col1) consists of some values and empty cells. In the actual file Tab1Col1 values are derived from other formula columns.

-Table 2 (Column: Tab2Col1) should display unique values from Table 1 excluding empty cells

Attached Sample Workbook for your reference.

Formula in Tab2Col1

=IFERROR(INDEX(Table1[Tab1Col1], MATCH(0, IF(ISBLANK(Table1[Tab1Col1]), 1, COUNTIF(F$3:$F4, Table1[Tab1Col1])), 0)),"")

Could you please advise.

Thanks & regards,
Don

 

[rahulshewale1]

hiii,

please find the attached formula base solution i hope it will help you.

=IF(ROW()-22>$B$37,"",VLOOKUP(ROW()-22,$B$23:$C$37,2,FALSE))


thanks
rahul shewale

 

[bosco_yip]

Hi,

1] Try this array formula (confirm entered with CTRL+SHIFT+ENTER)

=IFERROR(INDEX(Table1[Tab1Col1],SMALL(IF(Table1[Tab1Col1]<>"",ROW(C$4:C$19)-ROW(C$4)+1),ROWS($1:1))),"")

2] Or this non-array formula

=IFERROR(INDEX(Table1[Tab1Col1],AGGREGATE(15,6,(ROW(C$4:C$19)-ROW(C$4)+1)/(Table1[Tab1Col1]<>""),ROWS($1:1))),"")

3] All copy down

4] Please refer to the attachment

Regards

 

[inddon]

Hello Bosco_yip

Thank you for your help. With this post I have learned something new from you.

I ended up making too many helper columns in Table2. I tried to do the rest of the formulas, but somehow got lost.

Table2
Here, I just copy and paste the values from columns Container to Amounts.
There are helper columns from which to state which rows values (column ContBill) should be included in the final output. The values of ContBill are shown in Table3. (With the help of your previous solution)

Could you please review and suggest a better way of accomplishing this requirement (attached a sample workbook). It describes the explanation and the desired output.


Look forward to hearing from you.

Regards,
Don

 

[inddon]

Hi,

1] Try this array formula (confirm entered with CTRL+SHIFT+ENTER)

=IFERROR(INDEX(Table1[Tab1Col1],SMALL(IF(Table1[Tab1Col1]<>"",ROW(C$4:C$19)-ROW(C$4)+1),ROWS($1:1))),"")

2] Or this non-array formula

=IFERROR(INDEX(Table1[Tab1Col1],AGGREGATE(15,6,(ROW(C$4:C$19)-ROW(C$4)+1)/(Table1[Tab1Col1]<>""),ROWS($1:1))),"")

3] All copy down

4] Please refer to the attachment

Regards

Hi Bosco_yip,

There is slight problem with the formula. When I extend the table for example with 20 rows, the formula gets copied down but the below value still remains at C$19, whereas it should increase with the total number of the table rows.

=IFERROR(INDEX(Table1[Tab1Col1],SMALL(IF(Table1[Tab1Col1]<>"",ROW(C$4:C$19)-ROW(C$4)+1),ROWS($1:1))),"")

Could you please advise, how this can be achieved?

Many thanks and regards,
Don

 

[bosco_yip]

Try to use this CSE formula instead :

=IFERROR(INDEX(Table2[ContBill],SMALL(IF(Table2[ContBill]<>"",ROW(Table2[ContBill])-ROW(Table2[[#Headers],[ContBill]])),ROWS($1:1))),"")

Regards

 

[inddon]

Thanks Bosco_yip.

Regards,
Don