Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
counting values only once
0 views
Skip to first unread message
Keith H
Nov 11, 2009, 5:19:02 AM11/11/09
to
I have a problem and was wondering if there was a way to solve it that I
haven’t thought of. I have a column that represents months. It stores then in
numeric format, i.e. 1 for January, 2 for February etc… The next column is a
reference to a machine, in the format 001/0019 or 005/0041 and so on.
Now one machine can come up in the same month more that once. I want a pivot
table that shows me the total number of machines in each month, but not count
the same machine more that once in any month.
I managed this by sorting the two columns first by month then by machine. I
then added a third column with the following formula
=IF(AND(E2=E3,D2=D3),0,1) so that the month and machine values are compared
to the next ones.
This works, but the person requesting a solution says he doesn’t want the
columns sorted. Is there a way to do this without sorting the columns?
haven’t thought of. I have a column that represents months. It stores then in
numeric format, i.e. 1 for January, 2 for February etc… The next column is a
reference to a machine, in the format 001/0019 or 005/0041 and so on.
Now one machine can come up in the same month more that once. I want a pivot
table that shows me the total number of machines in each month, but not count
the same machine more that once in any month.
I managed this by sorting the two columns first by month then by machine. I
then added a third column with the following formula
=IF(AND(E2=E3,D2=D3),0,1) so that the month and machine values are compared
to the next ones.
This works, but the person requesting a solution says he doesn’t want the
columns sorted. Is there a way to do this without sorting the columns?
Douglas J. Steele
Nov 11, 2009, 6:33:53 AM11/11/09
to
From the formula you posted, it would appear that you're using Excel.
This newsgroup is for questions about Access, the database product that's
part of Office Professional. Assuming you are using Excel, you'd be best off
reposting to a newsgroup related to Excel.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Keith H" <Kei...@discussions.microsoft.com> wrote in message
news:F63B1B14-D6BF-4E53...@microsoft.com...
>I have a problem and was wondering if there was a way to solve it that I
> haven't thought of. I have a column that represents months. It stores then
> in
> numeric format, i.e. 1 for January, 2 for February etc. The next column is
Keith H
Nov 11, 2009, 9:48:01 AM11/11/09
to
Sorry about that. I thought I had posted it in the Excel group.
"Douglas J. Steele" wrote:
> .
>
0 new messages