Another Stupid Excel Question

[Kevy Baby]

Actually, two.


Why the heck does the "Reviewing" toolbar keep popping up? I keep turning it off (View > Toolbars), but it keeps coming back. I went to the "Customize" menu (View > Toolbars > Customize), clicked the "Options" tab and clicked the "Reset menu and toolbar usage data" button. Still didn't make it stay away. It drives me crazy
How difficult is it to dedupe using Excel? Or is it even possible?

[BarTopDancer]

1) It's Microsoft driving you nutz.

2) I don't think it's possible. We used sort and a filter but eventually imported them into a sql database and deduped through there.

[Ghoulish Delight]

I don't know, but if you're using Excel 2007, make sure none of your multiplication results in an answer that's close to 65535.

[JWBear]

I don't know, but if you're using Excel 2007, make sure none of your multiplication results in an answer that's close to 65535.

Why? What happens?

[Alex]

Actually, two.

Why the heck does the "Reviewing" toolbar keep popping up? I keep turning it off (View > Toolbars), but it keeps coming back. I went to the "Customize" menu (View > Toolbars > Customize), clicked the "Options" tab and clicked the "Reset menu and toolbar usage data" button. Still didn't make it stay away. It drives me crazy
How difficult is it to dedupe using Excel? Or is it even possible?

Don't know on #1, I'd have to be at work to play with it and maybe figure it out.

On #2, I think this will work:

1. Highlight the column you want to use use as the key (if it needs to be some combination of columns, the easiest thing is to probably create a new column that simply concatenates the columns into a single value.

2. Go to Data --> Filter --> Advanced Filter. There should be a checkbox on that for something like "Unique values only" which should filter out the duplicates.

If you want the then truly have the dupes gone, highlight the visilble cells with your filtered data and hit alt-; (alt and semicolon) which copies visible cells (and not the hidden ones in between), go to a separate worksheet and paste it in. Then you can delete or stop using the first worksheet.

[Alex]

65535 is the largest 16-bit number when expressed in base-10. Excel 2007 has a bug in its math engine that sometimes shows up when doing things around that number. It was just found this week I believe (I'm assuming since I've seen it mentioned several times this week all of a sudden).

[Ghoulish Delight]

65535 is the largest 16-bit number when expressed in base-10. Excel 2007 has a bug in its math engine that sometimes shows up when doing things around that number. It was just found this week I believe (I'm assuming since I've seen it mentioned several times this week all of a sudden).
Yup. It's apparently only a display problem, not really a math problem. If you feed the result of such an equation as input to another equation, it works fine. But if your final answer to be displayed is either 65,535 or 65,536 (or apparently within a few decimal places thereof), the engine that converts it into displayable text loses its brains and displays "100,000" as the answer.

[Kevy Baby]

On #2, I think this will work:

1. Highlight the column you want to use use as the key (if it needs to be some combination of columns, the easiest thing is to probably create a new column that simply concatenates the columns into a single value.

2. Go to Data --> Filter --> Advanced Filter. There should be a checkbox on that for something like "Unique values only" which should filter out the duplicates.

If you want the then truly have the dupes gone, highlight the visible cells with your filtered data and hit alt-; (alt and semicolon) which copies visible cells (and not the hidden ones in between), go to a separate worksheet and paste it in. Then you can delete or stop using the first worksheet.This worked like a charm - thank you!

I also didn't know about the copying only visible cells - that was a great tip as well!

[BarTopDancer]

I also didn't know about the copying only visible cells - that was a great tip as well!

oo! I didn't either! Thank you Alex!

[JWBear]

Yup. It's apparently only a display problem, not really a math problem. If you feed the result of such an equation as input to another equation, it works fine. But if your final answer to be displayed is either 65,535 or 65,536 (or apparently within a few decimal places thereof), the engine that converts it into displayable text loses its brains and displays "100,000" as the answer.

Ok, thanks. I was worried that it might open a portal to another plain of existence or something.

[Kevy Baby]

1. Highlight the column you want to use use as the key (if it needs to be some combination of columns, the easiest thing is to probably create a new column that simply concatenates the columns into a single value.

2. Go to Data --> Filter --> Advanced Filter. There should be a checkbox on that for something like "Unique values only" which should filter out the duplicates.

If you want the then truly have the dupes gone, highlight the visilble cells with your filtered data and hit alt-; (alt and semicolon) which copies visible cells (and not the hidden ones in between), go to a separate worksheet and paste it in. Then you can delete or stop using the first worksheet.OK - actually used this today to dedupe 1921 records down to 1552 (I did it manually last time). Thank you again - I will be using this many times in the future.

One question though: you do you show the "hidden" rows again? I have Autofiltered the data then un-Autofiltered it. I am just wondering if there is a more direct way (un-hiding rows doesn't work).