2 bugs in Excel
Summarize or Subtotal as it is called in US English requires the data to be
sorted.
Regards,
Tom Ogilvy
"Paul" wrote in message
...
Bug #1: SUMMARIZE
I had a block of data 4,000 rows by 4 columns. Col A had
120 ID numbers, each repeated multiple times. I used the
DATA SUMMARIZE feature to compile the data so that the
4000 rows should have boiled down to 120 rows, one for
each ID.
I kept getting wierd results. For example, ID 12345 was
found in 212 rows. After summary, it should be only in one
summary row, but it would be in 4 summary rows, each with
wrong sums.
Solution: I sorted the 4000 rows first by ID#. Then
everything worked fine. Go figure. At least I had a
workaround.
BUG2: INDEX-MATCH
Col X and Y consist of a title list and next to it a code:
X....................Y
Travel................11111
Food..................22222
Taxes.................33333
Floor Maintenance.....44444
General Maintenance...55555
Electrical Maintenance.66666
etc.
CoL A cells have a drop-down list that is actually col X.
Col B cells get their data from col Y via a FIND-MATCH
formula. So if you select "Travel" in cell A1, B1
becomes "11111". It works 99.99% of the time. But if you
select "General Maintenance" from the list, you get the
wrong matching cell in B. If you change "General
Maintenance" to "GM", it matches fine!!!!.
It works fine with "GGGGGGG" and with "GGGGGG
Maintenance". It fails with "Gen GGGG Maintenance". If you
make it "xxx", it gives you <a different wrong match. So
it is going through the list and finding a match, but the
wrong match in a <few frustrating cases. This one has us
stumped.
Paul
|