• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 011 - distinct/duplicate/unique array from text AND numbers

Sajan

Excel Ninja
Hi Shrivallabha,

Yes, when I array-enter Jeff's formula over three rows, I get the expected results, but not when I try to use it in a single cell, as part of another formula.


However, Jeff in his most recent post indicated that he does NOT get #VALUE in Excel 2010 or 2013, whereas I am.


I have Excel 2010 on Windows 7, and Excel 2013 on Windows 8. I get the same results on both computers. (I have not changed any of my default settings... just the standard US settings.)


-Sajan.
 

Haseeb A

Active Member
Oh! Sam. Good approach. Never thought about LOOKUP :)


Jeff;


All 3 formulas successfully returning values in a Array range. (Excel 2007, 2010)

when use F9 showing arrays correctly. (Excel 2007, 2010)

When used in COUNTA return 1 (Excel 2007, 2010)

When use F9 showing it's correct count. (Only in Excel 2010)

When use F9 #N/A error. (Excel 2007)


When used &"ABC" return correctly in Array range. No issues. (Excel 2007, 2010)

When use F9 return #VALUE! error. (Excel 2007, 2010)

In COUNTA showing correct count in the range , which are the count of #VALUE! errors. (Excel 2007, 2010)


Both versions are running in Windows 7 with just normal US settings.
 

shrivallabha

Excel Ninja
Ah,


Even in a single cell you will have to CSE irrespective of whether you trying for one result or doing a COUNTA.


For what it's worth, I have Excel 2010 - 32 bit - Win XP.
 

jeffreyweir

Active Member
Playing around with my OFFSET(OFFSET mix, it seems that this is very very buggy. For instance, if you already have another OFFSET formula in the workbook, one or both may not return the correct result. And adding in some extra rows into the workbook sometimes fixes the issue, and sometimes not.


In short, steer clear of it.


By way of a demonstration, here's one weird thing that's been happening:

I put this in A1:F1 and named it d

1 2 3 A B C


In A2 I array entered =OFFSET(d,,{0,1,2,3,4,5},,)

When I evaluate this with F9, I get {1,2,3,"A","B","C"}, which is what I expect.


Then in A3 I array enter =OFFSET(OFFSET(d,,{0,1,2,3,4,5},,),,)

When I evaluate this with F9, I get {1,2,3,"A","B","C"}, which is what I expect.


THen in A4 I normally enter the same formula (i.e. I don't array enter it, but just push Enter).


When I evaluate this with F9, I get {1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

When I go back and evaluate A2 or A3 I get what I did before, i.e. {1,2,3,"A","B","C"}


Now when I go and evaluate A4 again, I get {1,2,3,"A","B","C"}


This is weird. And this isn't the only weird behavior I've been getting when OFFSET(OFFSET is in the sheet somewhere.
 

jeffreyweir

Active Member
Just found that Mike "Excel is fun" Girvin has recently traversed some of this territory.

Check out these links:

http://www.youtube.com/watch?v=T_EmoLfAJCM

http://www.youtube.com/watch?v=UbyiC-IZxDI

http://www.youtube.com/watch?v=7Rjh-LHvmCA
 

Stephen Crump

New Member
Hi Jeff


I've joined the discussion late, and haven't tried to follow why you're using OFFSET(OFFSET)...


but I replicate your weird spreadsheet behaviour, and apparently can fix it by tweaking =OFFSET(OFFSET(d,,{0,1,2,3,4,5},,),,) to

=OFFSET(OFFSET(d,,{0,1,2,3,4,5},,1),,)
 

jeffreyweir

Active Member
Thanks, Stephen. Nice to have you here in these discussions, by the way.


With that sorted, maybe my OFFSET(OFFSET approach becomes a viable way to dereference an OFFSET array again.


I've uploaded a sample file here, if anyone is interested:

https://www.dropbox.com/s/n824v8h62xuoa5s/Using%20OFFSET%20to%20dereference%20OFFSET_20130821.xlsb
 

Lori

Active Member
Was going back over previous challenges and found some great ideas here and elsewhere. Maybe the MODE.MULT function could also be put to use for these type of problems. For distinct entries in the list:

=LOOKUP(MODE.MULT(IF(MATCH(m,m,0)=(ROW(m)-MIN(ROW(m))+{1,1}),ROW(m))),ROW(m),m)
 

Sajan

Excel Ninja
Hi Lori,
That is a clever use of MODE.MULT to filter out stuff! With this challenge alone, I have developed a better appreciation for LOOKUP and MODE!

Following your idea, I am including the other two formulas using MODE.MULT for completeness:

Unique:
=LOOKUP(MODE.MULT(IF(COUNTIF(m,m)=1,ROW(m)*{1,1})),ROW(m),m)

Duplicate:
=LOOKUP(MODE.MULT(IF((COUNTIF(m,m)>1)*MATCH(m,m,0)=ROW(m)-MIN(ROW(m))+{1,1},ROW(m))),ROW(m),m)

-Sajan.
 

jeffreyweir

Active Member
Howdy Lori and Sajan. Just wanted to say that I appreciate you two keeping this thread alive. I've been too busy the last wee while to keep up with developments in this series, but am bookmarking this for future study when things quieten down.
 

Lori

Active Member
Hi Jeff - regarding OFFSET weird behaviour, i noticed you had quoted a couple of comments posted elsewhere further up this thread under my initials. I'm not sure about their validity but thanks!

Following on from Sajan's previous comment about CONVERT, it turns out many other engineering functions and several date functions also accept OFFSET or INDIRECT with arrays in arguments. These extra functions were only available in the analysis toolpak in prior versions and they have the feature that references containing more than one cell need to be coerced to values.

For example: =ISODD(A1:A2) returns an error but ISODD(--A1:A2) and ISODD(OFFSET(A1,{0;1},)) are both ok.

The only function that can return an array of text or numbers from a range of one or more columns without needing to be entered into multiple cells, by the looks of it, is DGET.

For example: =DGET(OFFSET(A2:B6,{1;3;4},{0;1;0},-2,1),1,C2:C3) where C2:C3 are both blank.

Entering: =INDEX(A2:B6,{2;4;5},{1;2;1}) into a range would be easier and more efficient though!
 

Lori

Active Member
For distinct values from a range of one or more columns containing text or numbers, maybe (Office 2010/13):

=DGET(OFFSET(INDIRECT(TEXT(MODE.MULT(IF(FREQUENCY(COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m),COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m))>={1,1},MODE.MULT(100000*ROW(m)+COLUMN(m),100000*ROW(m)+COLUMN(m)))),"r0c00000"),0),,,-2),1,OFFSET(m,ROWS(m),,2,1))

Notes:
  • - The range m is assumed to start in row 2 or below and contain blank rows following the last row.
  • - Replace >= by = or > for uniques or duplicates, respectively.
  • - Alternatively array-enter in a range: =CELL("Contents",INDIRECT(...,0)) instead of =DGET(OFFSET(INDIRECT(...,0)...)...) without the conditions on m above.
I managed to get distracted for most of the day with this one and no doubt there's other edge cases to account for but my heard hurts now.
 

Sajan

Excel Ninja
Hi Lori,
Thanks for this DGET formula.

I have never used any of the database functions in Excel, but had assumed that functions like DGET would be like a SQL Select statement, returning one or more items that match criteria. I was surprised and disappointed to read that it only returns one match. But thanks for the clever workaround you outlined, with OFFSET(...,-2).

Also gave me some additional ideas for determining row-wise MAX and MIN in a matrix.

By the way, not sure why you used INDIRECT(TEXT(...)) since DGET merely needs a reference. I got the same results with the following:
=DGET(OFFSET(m,MODE.MULT(IF(FREQUENCY(MATCH(m,m,0),MATCH(m,m,0))>={1,1},ROW(m)-MIN(ROW(m)))),0,-2),1,OFFSET(ZZ1,,,2,1))

Also, interesting technique with INDIRECT(TEXT...),0) as well to get R1C1-style references.

Regards,
Sajan.
 

Lori

Active Member
Hi Sajan, thanks for the positive feedback. Your formula works well for the single column case as described in the original challenge and is more efficient than mine. My intention had been to allow m to be a rectangular array (eg A2 : D9) hence the extra complications but maybe there's a simpler way?
 

Lori

Active Member
I decided to post an update here after stumbling upon this link (admittedly not one for the faint-hearted!):
http://excelxor.com/2014/10/09/coercing-array-returns-from-cse-resistant-formulas/
http://excelxor.com/2014/10/09/coercing-array-returns-from-cse-resistant-formulas/
The article appears to resolve some of the issues raised here. Among other things it demonstrates you can return valid arrays with INDEX using the following trick (although i still have little clue as to how this works):

=INDEX(A1:A5,N(IF(1,ROW(A1:A5))))

So for example Jeff's suggestion in post #6 above can be modified to this:

=INDEX(A:A,N(IF(1,SMALL(IF(FREQUENCY(MATCH(m,m,),MATCH(m,m,))>0,ROW(m)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(m,m))))))))

And for getting a unique array from any rectangular range the awkward DGET formula above can be replaced with:

=CELL("Contents",INDIRECT(TEXT(N(IF(1,MODE.MULT(IF(FREQUENCY(COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m),COUNTIF(m,"<="&m)+ISTEXT(m)*COUNT(m))>={1,1},MODE.MULT(100000*ROW(m)+COLUMN(m),100000*ROW(m)+COLUMN(m)))))),"r0c00000"),0))
 

jeffreyweir

Active Member
THat's a great link, Lori. Hadn't come across that blog before. Ok, I've got some serious reading to do on the above.
 
Top