Excel Help
 

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.

I've used this tool for years

http://www.ablebits.com/excel-remove...ates/index.php

Use the filter tool and it will highlight all the unique numbers

I want the output to be all the unique numbers in one column

Use the filter tool to find the unique numbers and then use an "if then" statement to move them. Im on a tablet and cant see your example but that should work

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.

Be careful using filters. If you use filters and copy cut paste it into another sheet, your data is going to be all messed up if you don't hit "visible cells only."

This is very confusing. I have no idea what I am doing or how to use pivot tables.

Do you only need unique numbers in a single columns or all columns?

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.

I don't know how to attach files but I got 86 unique numbers

as was also suggested a pivot table could get you what you need as well

Hmmm, I imported the file into Access and ran a simple grouped query and got 60 unique rows.

Attached are my results in Sheet2.

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.

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

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.

Learn VBA

Thanks, I'll get right on that! :thumb:






:rolleyes:

then your best bet is using a pivot table

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.

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

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.

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

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

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

This!

Yup.

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

I should be able to edit the script for my purposes, but how do I get that to run in my spreadsheet?

Never mind. I figured it out and it does exactly what I need.

Much rep to all who helped out!

Awesome. Glad it worked!

Highlight and use remove duplicates in data tab.

On my phone so not downloading the file to look at.