PDA

View Full Version : Computers Excel Help


Dartgod
11-20-2013, 07:39 AM
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.

scho63
11-20-2013, 07:43 AM
I've used this tool for years

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

HayWire
11-20-2013, 07:52 AM
Use the filter tool and it will highlight all the unique numbers

Dartgod
11-20-2013, 07:54 AM
I want the output to be all the unique numbers in one column

HayWire
11-20-2013, 08:01 AM
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

CleveSteve
11-20-2013, 10:00 AM
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.

chiefzilla1501
11-20-2013, 10:09 AM
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."

Dartgod
11-20-2013, 10:16 AM
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 is very confusing. I have no idea what I am doing or how to use pivot tables.

KC_Lee
11-20-2013, 10:28 AM
Do you only need unique numbers in a single columns or all columns?

Wildcat2005
11-20-2013, 10:33 AM
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.

Wildcat2005
11-20-2013, 10:36 AM
I don't know how to attach files but I got 86 unique numbers

Wildcat2005
11-20-2013, 10:41 AM
as was also suggested a pivot table could get you what you need as well

KC_Lee
11-20-2013, 10:42 AM
I don't know how to attach files but I got 86 unique numbers

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

KC_Lee
11-20-2013, 10:43 AM
Hmmm, I imported the file into Access and ran a simple grouped query and got 60 unique rows.

Attached are my results in Sheet2.

Rain Man
11-20-2013, 10:45 AM
Data Menu - Remove Duplicates. It's almost as if it's designed specifically for this situation.

Radar Chief
11-20-2013, 10:51 AM
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
11-20-2013, 10:53 AM
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
11-20-2013, 10:55 AM
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
11-20-2013, 10:55 AM
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
11-20-2013, 10:56 AM
Learn VBA

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






:rolleyes:

Wildcat2005
11-20-2013, 10:56 AM
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
11-20-2013, 10:57 AM
Thanks, I'll get right on that! :thumb:






:rolleyes:

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
11-20-2013, 10:58 AM
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
11-20-2013, 11:13 AM
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
11-20-2013, 11:29 AM
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
11-20-2013, 11:30 AM
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
11-20-2013, 12:03 PM
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
11-20-2013, 12:29 PM
:banghead:
SHIT.. lol. yeah, I didn't think of that.

58-4ever
11-20-2013, 12:35 PM
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
11-20-2013, 01:15 PM
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/2008/12/11/create-unique-list-from-selected-cells/

Dartgod
11-20-2013, 02:07 PM
Yup.

http://dailydoseofexcel.com/archives/2008/12/11/create-unique-list-from-selected-cells/

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

Dartgod
11-20-2013, 02:50 PM
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!

DaFace
11-20-2013, 03:50 PM
Never mind. I figured it out and it does exactly what I need.

Much rep to all who helped out!

Awesome. Glad it worked!

Valiant
11-20-2013, 04:54 PM
Highlight and use remove duplicates in data tab.

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