Can I extract unique cell values from every nth column?

[Ed from AZ]

I have a range that covers B5:XA160. For each row, data is entered in
11-cell groups: date, some numbers, description (a text value), more
numbers, and more numbers. At first it was enough to merely count how
many times certain descriptions appeared, because those were the only
ones we would see - or so the story went. Now, I need to extract the
unique descriptions AND provide a count!

Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the
number of times the expected descriptions appear, and by subtracting
these from the total number of text values I get a count of "Other".

Desc1: =SUMPRODUCT(--(ISNUMBER(SEARCH("Desc1",B5:XA5))))
(This one only searches the top row because the value is repeated
all the way down.)

Desc2: =SUMPRODUCT(--(ISNUMBER(SEARCH("Desc2",$B$5:$XA$160))))
(This one searches the whole range.)

Other: =(SUMPRODUCT(ISTEXT($B$5:$XA$160)*1))-((SUMPRODUCT(--(ISNUMBER
(SEARCH("Desc1",$B$5:$XA$160)))))+(SUMPRODUCT(--(ISNUMBER(SEARCH
("Desc2",$B$5:$XA$160))))))
(This one searches the whole range for all text values, then
subtracts the known values.)

Now, though, I need to show what these "Other" values are and provide
a count. Is that possible without building an array in VBA?

Ed

 

[Shane Devenshire]

Hi,

Try something like this:
=SUMPRODUCT(1/COUNTIF(Range,Range))
for counting uniques

To get a list of uniques use Data, Filter, Advanced Filter, Unique Records

 

[Ed from AZ]

Hi, Shane. Thanks for the response.

To get a list of uniques use Data, Filter, Advanced Filter, Unique Records

This won't work - I need a formula that will return unique records -
if that is possible, that is. I need to keep a running summary on
several different sheets in one workbook, and I need to pass this off
to people who need the data. They are not going to filter for their
data - they expect me to have it summarized (else why are they paying
me??). And I'd like to get away from having to manually filter and
enter every time they call for the summary data.

I'd also like to stay away from any kind of code in the workbook -
many computers are set up to either refuse code to run or even reject
an email with a coded attachment.

If this can't be put into a formula, then I guess I'll have to stay
manual.

Ed

 

[Max]

.. a range that covers B5:XA160

.. need to extract the unique descriptions AND provide a count

Here's an example of a pure formulas set up (uses 3 cols) that will
dynamically extract uniques from a multi-col source range and output into a
single col. It might appeal to you.

Assume a source range with anchor cell B5 and 3 cols, eg: B510

In F1:
=OFFSET($B$5,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy F1 down to extract the multi-col source range into a single col. Copy
down to F18* to cover the full extent of the source data matrix
*B510 has 6 rows x 3 cols = 18 cells

Adapt the anchor cell B5 and the "3" in the INT and MOD parts (3 = # of
source cols) to suit your actual source range, eg: B5:XA160? I don't have
xl07 (max col in xl03 is only col IV), but it should work just as well in
your xl07.

Then
In G1: =IF(F1=0,"",IF(COUNTIF(F$1:F1,F1)>1,"",ROW()))
In H1: =IF(ROW()>COUNT(G:G),"",INDEX(F:F,SMALL(G:G,ROW())))
Copy G1:H1 down to the extent in col F. Minimize cols F & G. Col H will
return the required uniques list, all neatly bunched at the top.

And if you need the count of the uniques extracted into col H,
just use in say I1: =COUNT(H:H)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Max]

Correction to this part:

And if you need the count of the uniques extracted into col H,
just use in say I1: =COUNT(H:H)

Use something like this to count the uniques in col H:
=SUMPRODUCT(--(H1:H1000<>""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Ed from AZ]

Max, this is absolutely awesome!!

I seem to have done smoething wrong, though. Here's how mine looks:

I used a range from B5:AH160, which is 33 cols x 156 rows = 5148
cells. I started all these formulas in row 166.
-- I numbered 1, 2, 3 ... 5148 in H166:H5313.
-- In I166, I put =OFFSET($B$5,INT((ROWS($1:1)-1)/33),MOD(ROWS
($1:1)-1,33)) and ran that down. It gave me everything in my range
down a single column.
-- Because I only need unique text values, and everything else is a
number or a date, I put =IF(ISTEXT(I166),I166,"") in J166 and ran it
down. It gave me just text values in J with everything else blank.
-- In K166, I put =IF(J166="","",IF(COUNTIF(J$166:J166,J166)>1,"",ROW
())). This gave me the row number of the first occasion of each text
value in J.

So far, so good! In fact, just screamin' excellent!!! Then . . .

-- In L166, I put =IF(ROW()>COUNT(K166:K5313),"",INDEX(J166:J5313,SMALL
(K166:K5313,ROW()))). I get nothing but blank cells. I also tried =IF
(ROW()>COUNT(K166:K5313),"",INDEX(I166:I5313,SMALL(K166:K5313,ROW())))
- also blank cells.

Did I mess it up by putting in that extra column for only text values?

Ed

 

[Max]

These 2 slight adjustments to J166 and L166
should drive it out correctly for you, Ed

In J166, copied down:
=IF(J166="","",IF(COUNTIF(J$166:J166,J166)>1,"",ROWS($1:1)))

In L166, copied down:
=IF(ROWS($1:1)>COUNT(K$166:K$5313),"",INDEX($J$166:$J$5313,SMALL(K$166:K$5313,ROWS($1:1))))

Basically, replace ROW() with ROWS($1:1)
and fix the ranges: K$166:K$5313, $J$166:$J$5313

ROWS($1:1) enables an easier to cross-relate flagging/reading between the
criteria/extract cols since it'll always start with: 1 irrespective of where
it's placed in (unlike ROW() which is sensitive), while the explicit ranges
now need to be fixed for copying down (unlike the earlier, neater looking
entire col ranges which doesn't need to be).
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
Max, this is absolutely awesome!!

I seem to have done something wrong, though. Here's how mine looks:

I used a range from B5:AH160, which is 33 cols x 156 rows = 5148
cells. I started all these formulas in row 166.
-- I numbered 1, 2, 3 ... 5148 in H166:H5313.
-- In I166, I put =OFFSET($B$5,INT((ROWS($1:1)-1)/33),MOD(ROWS
($1:1)-1,33)) and ran that down. It gave me everything in my range
down a single column.
-- Because I only need unique text values, and everything else is a
number or a date, I put =IF(ISTEXT(I166),I166,"") in J166 and ran it
down. It gave me just text values in J with everything else blank.
-- In K166, I put =IF(J166="","",IF(COUNTIF(J$166:J166,J166)>1,"",ROW
())). This gave me the row number of the first occasion of each text
value in J.

So far, so good! In fact, just screamin' excellent!!! Then . . .

-- In L166, I put =IF(ROW()>COUNT(K166:K5313),"",INDEX(J166:J5313,SMALL
(K166:K5313,ROW()))). I get nothing but blank cells. I also tried =IF
(ROW()>COUNT(K166:K5313),"",INDEX(I166:I5313,SMALL(K166:K5313,ROW())))
- also blank cells.

Did I mess it up by putting in that extra column for only text values?

Ed

 

[Max]

Oops, typo: "In J166" should read "In K166" in the earlier, viz.:

In K166, copied down:
=IF(J166="","",IF(COUNTIF(J$166:J166,J166)>1,"",ROWS($1:1)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Ed from AZ]

Max:

Sorry to keep you hanging on this. I was out for a few days and got
caught palying catch-up. This does indeed do everything I hoped for.
Thank you so much. When I have time, I want to go back and more
closely examine what you did so I can better understand what's
happening - but for right now it's just great that it works!

Again, many thanks.
Ed

 

[Max]

Good to hear, thanks for closing off here.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
Max:

Sorry to keep you hanging on this. I was out for a few days and got
caught palying catch-up. This does indeed do everything I hoped for.
Thank you so much. When I have time, I want to go back and more
closely examine what you did so I can better understand what's
happening - but for right now it's just great that it works!

Again, many thanks.
Ed