Excel Help

[Dartgod]

I need some help with excel that could save me quite a bit of time.

I have a spreadsheet of numbers, in 19 columns and over 200 rows. There are many duplicates in both columns and rows. I need a quick way to give me only the unique numbers.

EDIT: I have attached an example.

[Radar Chief]

Originally Posted by Rain Man

Data Menu - Remove Duplicates. It's almost as if it's designed specifically for this situation.

By people that have no idea how the average user will interface with their program.

[Dartgod]

Originally Posted by Wildcat2005

Have you tried moving all the numbers into one column and then removing duplicates?

Just cut/paste a column of numbers in into the next column. Just work your way down until all of them have been pasted into the first column.
I would start on the far right column and work that way, it will be easier.

ctrl+shift will be your friend here to highlight all the numbers in a column quickly

I just did this and it took less than 5 min.

This is how I do it now. I don't want to take 5 minutes. I want to do it in seconds.

[Dartgod]

Originally Posted by Rain Man

Data Menu - Remove Duplicates. It's almost as if it's designed specifically for this situation.

It still leaves duplicate values, plus they are all still in separate columns.

To do it now, I move all the columns into a single column and then use the Remove Duplicates button. Too tedious.

[KC native]

Originally Posted by Dartgod

This is how I do it now. I don't want to take 5 minutes. I want to do it in seconds.

Learn VBA

[Dartgod]

Originally Posted by Race Card native

Learn VBA

Thanks, I'll get right on that!

[Wildcat2005]

Originally Posted by Dartgod

This is how I do it now. I don't want to take 5 minutes. I want to do it in seconds.

then your best bet is using a pivot table

[KC native]

Originally Posted by Dartgod

Thanks, I'll get right on that!

Well, you did ask for the advice. Some light VBA coding would make it a routine process.

Hell you could probably google what you're looking for and find the VBA code too.

[KC_Lee]

Originally Posted by Dartgod

This is how I do it now. I don't want to take 5 minutes. I want to do it in seconds.

If you can get your hands on a copy of MS Access and learn some very, very basic queries your prayers would be answered.

[Dartgod]

Originally Posted by Race Card native

Well, you did ask for the advice. Some light VBA coding would make it a routine process.

Hell you could probably google what you're looking for and find the VBA code too.

I'll see what I can find. Thanks.

For the pivot table suggestion, I'm still not sure how that works. Can someone elaborate?

[Mosbonian]

Originally Posted by Dartgod

I'll see what I can find. Thanks.

For the pivot table suggestion, I'm still not sure how that works. Can someone elaborate?

Are you using 2010? If so, it pretty much walks you thru the process.

[Dayze]

Just so you don't have to get familiar with Pivot Tables and formula arrays etc this moment, you can do a quick work around until you get to that point.


1. Get all your data into one column
2. Insert row at Row 1 and label cell A1 anything; for example "a". It's needed to accomplish this but you can delete it afterwards.
Select entire column, copy, and paste into a new tab (just so it’s easier to look at for you) column A
3. Highlight column A
4. Next to the Filter icon, you should see an ‘advanced’ button; click this.
5. Click the ‘Unique records only” box
6. Click the ‘Copy to another location’ radio button
7. Click within the “Copy To” box, then simply click in the spread sheet where you want the data.
8. Click ok.
9. Boom

[Dartgod]

Originally Posted by Dayze

Just so you don't have to get familiar with Pivot Tables and formula arrays etc this moment, you can do a quick work around until you get to that point.


1. Get all your data into one column

Once I get to this point, it's just as easy to use the Remove Duplicates function.

[Dayze]

SHIT.. lol. yeah, I didn't think of that.

[58-4ever]

Originally Posted by CleveSteve

Select your data set

Insert a Pivot Table on a new sheet.

Drag your field which you need to be unique into the "Row labels" column.

That's it.

This!

[DaFace]

Originally Posted by Race Card native

Well, you did ask for the advice. Some light VBA coding would make it a routine process.

Hell you could probably google what you're looking for and find the VBA code too.

Yup.

http://dailydoseofexcel.com/archives...elected-cells/