Formulas Instead of Advanced Filter - More help

  • Thread starter Thread starter moily
  • Start date Start date
M

moily

Hi there,

I originally posted a request for help a few days ago but it's gotten
buried, I'm reposting hoping someone can help that hasn't seen it. I've
pasted all information from the previous thread as there had been one
suggestion that didn't quite fit:


Hi,

If I understand it correctly your suggestion would create 300 unique answers
though wouldn't it? Please tell me if I'm misunderstanding though!

Basically, I have 7 original options, 4 of those options are found in the
first column. The other columns would repeat many of the popular 4 but the 3
others can also be found in one or a few of the later columns. Example
(simplified to 5 options instead of 7 and 3 columns instead of 11):

Column1: Column 2: Column 3:
Yes No Maybe
No Yes Yes
Maybe Maybe No
No Whenever Anytime
No Maybe Whenever

I need to have a list of:
Yes
No
Maybe
Whenever
Anytime

Concatenate would create the following which would make all the rows unique
answers:
YesNoMaybe
NoYesYes
MaybeMaybeNo
NoWheneverAnytime
NoMaybeWhenever
 
Perhaps try a simpler formulas approach along these lines
(it will deliver the required outputs)

First, reduce the multi-col source range to a single col range, then bolt on
2 adjacent cols of relatively simple formulas to eke out & pack up the full
list of uniques

Assume your sample 3 col data is in A2:C2 down
In E2:
=OFFSET($A$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy E2 down until zeros are returned signalling exhaustion of data.
This reduces the 3 source cols into a single col

Modify the OFFSET to suit:
A2 is the anchor/top left cell of the source range
"3" in both INT/MOD parts is the number of source cols

Then place
In F2: =IF(E2=0,"",IF(COUNTIF(E$2:E2,E2)>1,"",ROW()))
This is the criteria col to flag the unique items in col E. Leave F1 empty.

In G2:
=IF(ROWS($1:1)>COUNT($F:$F),"",INDEX(E:E,SMALL($F:$F,ROWS($1:1))))
Select F2:G2, copy down, and all the unique items will appear listed at the
top in col G. Hide away/minimize cols E/F.

Modify the above to suit your actuals
Pl mark it by clicking the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
Hi Max,

Thank you so much for responding! Your solution is interesting and
ultimately usable but a bit impractical and unwieldy because of the amount of
data I work with. I've usually got 500 rows with 7 columns of data for each
of 5 questions. The first formula you gave would take up approx 3500 rows of
data and there would be 15 extra columns. I was hoping for (even if
complicated) a formula in one column for each question.

Cheers,
Ann
 
Back
Top