THESHADOWBOX.NET

Please login or register.

Login with username, password and session length
Advanced search  

News:

Amanda's got a blog. If blogs could talk, it'd ask you to read it. It might even say "please."

Pages: [1]   Go Down

Author Topic: Microsoft Excel experts I need your help!  (Read 4774 times)

0 Members and 1 Guest are viewing this topic.

Kenny Wisdom

  • Desire in a Bowl of Potatoes
  • Hero Member
  • *****
  • Offline Offline
  • Gender: Male
  • Posts: 1348
  • Emmett does peace; his way.
Microsoft Excel experts I need your help!
« on: November 14, 2008, 06:18:40 AM »

Let’s say I’ve got two columns for arguments sake. In column A there is always TEXT (actually a reference number with Alpha/Numeric) and in column B there are blanks and non-blanks of text.

I want to do a couple of things. Firstly, I want to count everything in column A, regardless of what’s in column B. However, entries in column A may be repeated according to what is in column B – so I need to use advanced filter and ask column A to display “Unique Records” only – I trust that this is correctly returning only one example of each.

Using COUNTA isn’t sophisticated enough because that will count hidden cells and I don’t want those included.
So, I am using this convoluted formula, which seems crazy. I just want to count VISIBLE ROWS as unique entries according to whether I expand or contract the rows!

Formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A*,ROW(A*:A*)-ROW(A*),0)),--(A*:A*<>””))

A* denotes Column A, (row number) e.g. A2:A500.

That’s one hell of a formula just to get Excel to count 1,2,3,4...! But it does actually count something!
The second problem occurs when I want to count only the number of entries in column A with non-blanks in column B – if I use the AUTOFILTER on column B it will change that column, but the rule for UNIQUE RECORDS on column A is ignored, so I might get duplicates returning a false count.  That and the formula at the foot of column A disappears. Help!

What’s the solution? How can I count unique records only, including blanks and then non-blanks without getting a false result and the formula must remain in place, recalculating every time the column expands or contracts? It must be a formula that counts TEXT entries, not values.

Logged
http://www.diggers.org/overview.htm

A life played for keeps. Read it, dig it, man...

Laissez lire, et laissez danser; ces deux amusements ne feront jamais de mal au monde!!

Greg Nova wrote:
Harper tu n'es qu'un petit couillon!

McMullet

  • All things bright and beauti-
  • Full Member
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 162
Re: Microsoft Excel experts I need your help!
« Reply #1 on: November 14, 2008, 09:31:18 AM »

I'm not 100% sure I understand the problem, but here's how I do what I think you're trying to do...

First, make a third column with this equation: =SUM(IF(A1=$A$1:$A$100,1,0)) and make it an array formula before filling down (i.e., press ctrl-shift-enter instead of enter). This should tell you how many instances of the given entry there are, so for example if the entry in A54 is copied 3 more times, C54 will say 3.

Example:

A .. 3
C .. 1
A .. 3
B .. 2
B .. 2
A .. 3
D .. 1

Then you make another array formula: =SUM(IF(1=A1:A100,1,IF(0=A1:A100,0,1/A1:A100)))

That will count the number of unique entries.

It's not the neatest way, but it works.
Logged
Like slack-jawed never Gnyaarg. Some then folks'll folks'll skunk, some. the but, again Yokel eat a Cletus.
Quote
Happy Dog loves to play fetch with a boy named Timmy! Timmy is Happy Dog's best friend. Timmy is made out of meat. Your whole family is made out of meat.

Kenny Wisdom

  • Desire in a Bowl of Potatoes
  • Hero Member
  • *****
  • Offline Offline
  • Gender: Male
  • Posts: 1348
  • Emmett does peace; his way.
Re: Microsoft Excel experts I need your help!
« Reply #2 on: November 14, 2008, 10:43:33 AM »

mm..I tried the first array formula and it just gave a result of "1" all the way down, even though I know there are duplicates.

I'll try and show you the problem:

H00003   W*** ***
H00003   V*** ***
H00009   
H00013   S*** ***
H00013   F*** ***
H00017   Y*** ***
H00019   
H00025   Q*** ***
H00028   P*** ***
H00028   G*** ***
H00041   
H00044   N*** ***
H00045   
H00047   
H00051   
H00052   
H00053   
H00055   Y*** ***
H00058   

In this example you will see some duplicates in column A e.g. H00003, H00013, H00028. This is quite correct because they have more than one TEXT item in column B.

However what I would want to be able to do is count every unique record in column A - which in this case would be 16 records.

Then I would want to know of those unique records how many have at least one TEXT item in column B. I just counted and would expect to see a result of 7 if this was working properly - it shouldn't count anything more than once - I just need to know yes, H00003 does have a corresponding entry in column B, and not that it has 2 entries.

It's doing my head in. Should be so simple but no, they haven't invented computers I can just talk to yet.
Logged
http://www.diggers.org/overview.htm

A life played for keeps. Read it, dig it, man...

Laissez lire, et laissez danser; ces deux amusements ne feront jamais de mal au monde!!

Greg Nova wrote:
Harper tu n'es qu'un petit couillon!

McMullet

  • All things bright and beauti-
  • Full Member
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 162
Re: Microsoft Excel experts I need your help!
« Reply #3 on: November 14, 2008, 05:18:25 PM »

I tried with your data and it works - are you sure you entered the formulae properly?

Paste the formula in, correct the cell reference and hit ctrl-shift-enter. Any time you edit the cell you have to hit ctrl-shift-enter again or it won't recognise it as an array formula. You can tell because it has {curly brackets} around it when you select the cell.

Not sure about the second part... that does sound trickier.
Logged
Like slack-jawed never Gnyaarg. Some then folks'll folks'll skunk, some. the but, again Yokel eat a Cletus.
Quote
Happy Dog loves to play fetch with a boy named Timmy! Timmy is Happy Dog's best friend. Timmy is made out of meat. Your whole family is made out of meat.

Haushinka

  • finally fell in love with THE WORLD.
  • Hero Member
  • *****
  • Offline Offline
  • Gender: Female
  • Posts: 1802
  • SHOVE IT SHOVE IT SHOVE IT.
Re: Microsoft Excel experts I need your help!
« Reply #4 on: November 14, 2008, 05:27:49 PM »

<a href="http://tinypic.com" target="_blank"><img src="http://i34.tinypic.com/2euismt.gif" border="0" alt="Image and video hosting by TinyPic">[/url]
Logged
Yoshiki Vázquez Baeza.
Future husband.

McMullet

  • All things bright and beauti-
  • Full Member
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 162
Re: Microsoft Excel experts I need your help!
« Reply #5 on: November 14, 2008, 05:32:41 PM »

That too.
Logged
Like slack-jawed never Gnyaarg. Some then folks'll folks'll skunk, some. the but, again Yokel eat a Cletus.
Quote
Happy Dog loves to play fetch with a boy named Timmy! Timmy is Happy Dog's best friend. Timmy is made out of meat. Your whole family is made out of meat.

Kenny Wisdom

  • Desire in a Bowl of Potatoes
  • Hero Member
  • *****
  • Offline Offline
  • Gender: Male
  • Posts: 1348
  • Emmett does peace; his way.
Re: Microsoft Excel experts I need your help!
« Reply #6 on: November 14, 2008, 06:21:52 PM »

Thanks for your help, I'll try again next week. If I don't go for option 2. Thanks, Haushy.
Logged
http://www.diggers.org/overview.htm

A life played for keeps. Read it, dig it, man...

Laissez lire, et laissez danser; ces deux amusements ne feront jamais de mal au monde!!

Greg Nova wrote:
Harper tu n'es qu'un petit couillon!

yosmark

  • The Powa. The Powa.
  • Hero Member
  • *****
  • Offline Offline
  • Gender: Male
  • Posts: 5884
Re: Microsoft Excel experts I need your help!
« Reply #7 on: November 14, 2008, 08:10:24 PM »

Excel is for pussies.
Logged

Kenny Wisdom

  • Desire in a Bowl of Potatoes
  • Hero Member
  • *****
  • Offline Offline
  • Gender: Male
  • Posts: 1348
  • Emmett does peace; his way.
Re: Microsoft Excel experts I need your help!
« Reply #8 on: November 14, 2008, 08:16:50 PM »

Thank you, Yossy.
Logged
http://www.diggers.org/overview.htm

A life played for keeps. Read it, dig it, man...

Laissez lire, et laissez danser; ces deux amusements ne feront jamais de mal au monde!!

Greg Nova wrote:
Harper tu n'es qu'un petit couillon!

McMullet

  • All things bright and beauti-
  • Full Member
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 162
Re: Microsoft Excel experts I need your help!
« Reply #9 on: November 14, 2008, 10:38:18 PM »

Excel is for pussies.
Well, naturally the manliest approach is to solve these problems using MS Paint.

Anyway, sticking to the pussy approach... The following works, even if it is a bit of a sledgehammer technique and needs more columns.

ABCDE
H00003W*** ***=SUM(IF(A1=$A$1:$A$19,1,0))=IF(B1=0,,A1)=SUM(IF(D1=$D$1:$D$19,1,0))

The equations in C and E need to be array formulae.

To get the number of unique entries in col. A:

=SUM(IF(1=C1:C19,1,IF(0=C1:C19,0,1/C1:C19)))

(array again)

The number of unique entries in A that have anything in B:

=SUM(IF(1=E1:E19,1,IF(0=E1:E19,0,1/E1:E19)))-1

(array)

When I do this I get 16 for the first formula and 7 for the second.
Logged
Like slack-jawed never Gnyaarg. Some then folks'll folks'll skunk, some. the but, again Yokel eat a Cletus.
Quote
Happy Dog loves to play fetch with a boy named Timmy! Timmy is Happy Dog's best friend. Timmy is made out of meat. Your whole family is made out of meat.

Kenny Wisdom

  • Desire in a Bowl of Potatoes
  • Hero Member
  • *****
  • Offline Offline
  • Gender: Male
  • Posts: 1348
  • Emmett does peace; his way.
Re: Microsoft Excel experts I need your help!
« Reply #10 on: November 21, 2008, 02:27:28 AM »

I'm thinking I need to take a crash course in pivot tables.
Logged
http://www.diggers.org/overview.htm

A life played for keeps. Read it, dig it, man...

Laissez lire, et laissez danser; ces deux amusements ne feront jamais de mal au monde!!

Greg Nova wrote:
Harper tu n'es qu'un petit couillon!

McMullet

  • All things bright and beauti-
  • Full Member
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 162
Re: Microsoft Excel experts I need your help!
« Reply #11 on: November 21, 2008, 09:40:43 AM »

Never used them, but that appears to be the sort of thing to do the job.

The array formulae will work OK if you're only interested in one thing, but it's a MacGyver duct tape job so it's probably better to learn the proper way.
Logged
Like slack-jawed never Gnyaarg. Some then folks'll folks'll skunk, some. the but, again Yokel eat a Cletus.
Quote
Happy Dog loves to play fetch with a boy named Timmy! Timmy is Happy Dog's best friend. Timmy is made out of meat. Your whole family is made out of meat.

Kenny Wisdom

  • Desire in a Bowl of Potatoes
  • Hero Member
  • *****
  • Offline Offline
  • Gender: Male
  • Posts: 1348
  • Emmett does peace; his way.
Re: Microsoft Excel experts I need your help!
« Reply #12 on: November 21, 2008, 11:27:00 AM »

Yep, I think you're right. I was looking at one load of data and of course that raised umpteen other questions which then I decided I needed to dig into, which meant loads of filters and scenarios and what ifs.

Thanks though - only by looking at the problem from the array formula point of view did I make that decision.
Logged
http://www.diggers.org/overview.htm

A life played for keeps. Read it, dig it, man...

Laissez lire, et laissez danser; ces deux amusements ne feront jamais de mal au monde!!

Greg Nova wrote:
Harper tu n'es qu'un petit couillon!

FRIEZENS GALORE

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 422
    • WERM WORM
Re: Microsoft Excel experts I need your help!
« Reply #13 on: December 22, 2016, 10:00:01 AM »

Kenny did you get your problem fixed?
Logged
you don't rise when people fall
Pages: [1]   Go Up