Duplicates, Comparison and Condensing
 

Hello everyone... I have a problem.

What I need to do is somewhat hard to put into words. I need to do two
things, at the same time, with a group of data. I have about 3,700
rows of data. Column 'B' has numeric codes in it. These are actually
DOE school codes (e.g. 001253, 002561, etc.). Each one of these codes
has about 5 columns associated with it. In these columns will be
either the number 0, 1, 2, or 3. Below is an example:

00125300 3 3 0 0 1
00125300 0 1 2 0 3
00125300 3 3 1 2 0
00125300 1 2 3 1 2
00125300 2 0 0 3 0

In this example, you have one school (00125300) listed 5 times due to
the school having 5 users. Each user has a different level of access
to certain tasks, which are represented by the numers 0-3. What I need
to do are the following:

1) Compare all five "users", and take the highest value and place in a
single row.

2) Identify that the school is listed 5 times, and delete 4 of the
duplicate entries.

So, in a nutshell, in the example above, I would would want to "end up"
with one row with the school code 00125300, and 5 columns of 3's...
Since each column has at least one 3 in it, that would be the highest
value.

00125300 3 3 3 3 3

So I need to recognize the duplicates, compare the values, take the
highest, put into one row, and delete the others. Impossible? It is
to me... Any help at this point would be greatly appreciated!! Thank
you all for taking the time to try to make sense of this!!

Duplicates, Comparison and Condensing
 

Maybe...
Add a new worksheet
back to the original sheet
Add some headers.
sort your data by column A (DOE codes)
Select your range and do Data|subtotals
Choose MAX as the function to use for each of those 5 columns

When you finish up, you'll see outlining symbols on the left hand side.
Use those to show just the subtotals (click the 2 box at the top)

Select the visible cells
edit|goto|special|check visible cells only

Copy those visible cells
Paste into that new workbook.

And then select column A of the new worksheet
edit|Replace
what: _Max (spacebar MAX)
with: (leave blank)
replace all

Get rid of the bold formatting



stacy wrote:

Hello everyone... I have a problem.

What I need to do is somewhat hard to put into words. I need to do two
things, at the same time, with a group of data. I have about 3,700
rows of data. Column 'B' has numeric codes in it. These are actually
DOE school codes (e.g. 001253, 002561, etc.). Each one of these codes
has about 5 columns associated with it. In these columns will be
either the number 0, 1, 2, or 3. Below is an example:

00125300 3 3 0 0 1
00125300 0 1 2 0 3
00125300 3 3 1 2 0
00125300 1 2 3 1 2
00125300 2 0 0 3 0

In this example, you have one school (00125300) listed 5 times due to
the school having 5 users. Each user has a different level of access
to certain tasks, which are represented by the numers 0-3. What I need
to do are the following:

1) Compare all five "users", and take the highest value and place in a
single row.

2) Identify that the school is listed 5 times, and delete 4 of the
duplicate entries.

So, in a nutshell, in the example above, I would would want to "end up"
with one row with the school code 00125300, and 5 columns of 3's...
Since each column has at least one 3 in it, that would be the highest
value.

00125300 3 3 3 3 3

So I need to recognize the duplicates, compare the values, take the
highest, put into one row, and delete the others. Impossible? It is
to me... Any help at this point would be greatly appreciated!! Thank
you all for taking the time to try to make sense of this!!

--

Dave Peterson

Duplicates, Comparison and Condensing
 

Or you could use a Pivot Table. Put the code in the Row Field and the
max of the access leve colums in the Data Field.

Regards
Rowan

stacy wrote:
Hello everyone... I have a problem.

What I need to do is somewhat hard to put into words. I need to do two
things, at the same time, with a group of data. I have about 3,700
rows of data. Column 'B' has numeric codes in it. These are actually
DOE school codes (e.g. 001253, 002561, etc.). Each one of these codes
has about 5 columns associated with it. In these columns will be
either the number 0, 1, 2, or 3. Below is an example:

00125300 3 3 0 0 1
00125300 0 1 2 0 3
00125300 3 3 1 2 0
00125300 1 2 3 1 2
00125300 2 0 0 3 0

In this example, you have one school (00125300) listed 5 times due to
the school having 5 users. Each user has a different level of access
to certain tasks, which are represented by the numers 0-3. What I need
to do are the following:

1) Compare all five "users", and take the highest value and place in a
single row.

2) Identify that the school is listed 5 times, and delete 4 of the
duplicate entries.

So, in a nutshell, in the example above, I would would want to "end up"
with one row with the school code 00125300, and 5 columns of 3's...
Since each column has at least one 3 in it, that would be the highest
value.

00125300 3 3 3 3 3

So I need to recognize the duplicates, compare the values, take the
highest, put into one row, and delete the others. Impossible? It is
to me... Any help at this point would be greatly appreciated!! Thank
you all for taking the time to try to make sense of this!!

Duplicates, Comparison and Condensing
 

Awesome! Perfect!! What else can I say!!!

This will work perfectly!! However, a couple of questions that I am
sure I am missing:

After the sorting took place, I got this:
Code Rank 1 Rank 2 Rank 3 Rank 4 Rank 5
00125300 3 3 0 0 1
00125300 0 1 2 0 3
00125300 3 3 1 2 0
00125300 1 2 3 1 2
00125300 2 0 0 0 0
00125300 Max 3 3 3 2 3
Grand Max 3 3 3 2 3

After I click '2' for the outline, I get this:
Code Rank 1 Rank 2 Rank 3 Rank 4 Rank 5
00125300 Max 3 3 3 2 3
Grand Max 3 3 3 2 3

After I copy the visible cells to a new workbook, and then select column
A of the new worksheet
edit|Replace
what: _Max (spacebar MAX)
with: (leave blank)
replace all... I get this:
Code Rank 1 Rank 2 Rank 3 Rank 4 Rank 5
125300 3 3 3 2 3
Grand 3 3 3 2 3

... My question(s) a
1) Can I get rid of the 'Grand' row all together?
2) I need to retain the leading zeros in the DOE code (assuming this is
just destination cell formatting)


I can't thank you enough for your help already Dave... INVALUABLE!!




*** Sent via Developersdex http://www.developersdex.com ***

Duplicates, Comparison and Condensing
 

You can just delete that Grand Max row (Or omit it from the range to copy).

And when you change _max to "", excel thinks that your just reentering numbers.

You could either give that range/column a nice custom format:
select the range
format|cells|number tab|custom category
00000000

But the value will still be 125300, but it'll look like 00125300.

Or you could insert a helper column and remove the _max that way:

Say you insert a new column A.
Then you can put this in A2 and drag down:
=substitute(b2," max","")
and drag down
Select the range
Edit|copy
select B2
edit|Paste special|values
and delete that helper column (column A).



Stacy McMillan wrote:

Awesome! Perfect!! What else can I say!!!

This will work perfectly!! However, a couple of questions that I am
sure I am missing:

After the sorting took place, I got this:
Code Rank 1 Rank 2 Rank 3 Rank 4 Rank 5
00125300 3 3 0 0 1
00125300 0 1 2 0 3
00125300 3 3 1 2 0
00125300 1 2 3 1 2
00125300 2 0 0 0 0
00125300 Max 3 3 3 2 3
Grand Max 3 3 3 2 3

After I click '2' for the outline, I get this:
Code Rank 1 Rank 2 Rank 3 Rank 4 Rank 5
00125300 Max 3 3 3 2 3
Grand Max 3 3 3 2 3

After I copy the visible cells to a new workbook, and then select column
A of the new worksheet
edit|Replace
what: _Max (spacebar MAX)
with: (leave blank)
replace all... I get this:
Code Rank 1 Rank 2 Rank 3 Rank 4 Rank 5
125300 3 3 3 2 3
Grand 3 3 3 2 3

.. My question(s) a
1) Can I get rid of the 'Grand' row all together?
2) I need to retain the leading zeros in the DOE code (assuming this is
just destination cell formatting)

I can't thank you enough for your help already Dave... INVALUABLE!!

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

Duplicates, Comparison and Condensing
 

Cool... Everything has worked perfectly... One final question if you
don't mind though... Is there an easy way to add the corresponding name
to the outlined "max" row? Meaning, I get the following:

Name Code A B C
Nelnet 00125300 3 2 0
Nelnet 00125300 0 1 3
Nelnet 00125300 1 0 1
00125300 Max 3 2 3

On the row with 'Max', is there an easy way to carry down the 'Nelnet'
name? I was thinking I could use vlookup on another sheet, and just
find corresponding DOE code, and place the name in there... THought I
would see if there may be a simpler, cleaner method though. Thanks
again for the enormous help. It is just was I was looking for...

Duplicates, Comparison and Condensing
 

Ah, you've changed what you wanted!

Hide just the details and just show those subtotal lines.
Select A2:Axxx (avoid the header and avoid the grandtotal line)
edit|goto|special|visible cells only.

Now notice the cell that you're in (look at the name box to the left of the
formulabar).

If you're in A19, then type:
=a18
and hit ctrl-enter
This will fill the selected cells with a formula that points at the previous
cell.

You can show all the data and then select the column and edit|Copy, edit|Paste
special|values if you want.

stacy wrote:

Cool... Everything has worked perfectly... One final question if you
don't mind though... Is there an easy way to add the corresponding name
to the outlined "max" row? Meaning, I get the following:

Name Code A B C
Nelnet 00125300 3 2 0
Nelnet 00125300 0 1 3
Nelnet 00125300 1 0 1
00125300 Max 3 2 3

On the row with 'Max', is there an easy way to carry down the 'Nelnet'
name? I was thinking I could use vlookup on another sheet, and just
find corresponding DOE code, and place the name in there... THought I
would see if there may be a simpler, cleaner method though. Thanks
again for the enormous help. It is just was I was looking for...

--

Dave Peterson

Duplicates, Comparison and Condensing
 

Yeah, i got busted!! But as always... PERFECT!! The
spreadsheet/report is done, and the people that it was sent to are
extremely pleased. I can't say thanks again for your help... I am sure
my name will be plastered all over this message board with questions in
the days to come!! :) Thanks again for everything!!

Duplicates, Comparison and Condensing
 

If you have to do this lots of times, you may want to follow Rowan's advice and
learn about pivottables.

You'll be able to see the summary (although you won't be able to see the details
as readily).

To read more about the pivottable stuff, you may want to look at some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

stacy wrote:

Yeah, i got busted!! But as always... PERFECT!! The
spreadsheet/report is done, and the people that it was sent to are
extremely pleased. I can't say thanks again for your help... I am sure
my name will be plastered all over this message board with questions in
the days to come!! :) Thanks again for everything!!

--

Dave Peterson