# Hopefully a simple Excel question **URL:** \ **Category:** Factual Questions\ **Created:** [February 25, 2007, 7:25pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632 "2007-02-25T19:25:19Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Future\_Londonite](https://avatars.discourse-cdn.com/v4/letter/f/9dc877/32.png) [@Future\_Londonite](https://boards.straightdope.com/u/Future_Londonite)\ **Post date:** [February 25, 2007, 7:25pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/1 "2007-02-25T19:25:19Z")
Hey guys - I need some Excel help. I have a list of 6700 rows with account numbers and descriptions. Most but not all are listed twice or more. What command (if there is one) can I use to sort/arrange the list so that only have each account number once? I need the date in such a way that I can copy/paste into another sheet. Thanks, ---
**Author:** ![ArdentAgentApples](https://avatars.discourse-cdn.com/v4/letter/a/4af34b/32.png) [@ArdentAgentApples](https://boards.straightdope.com/u/ArdentAgentApples)\ **Post date:** [February 25, 2007, 7:45pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/2 "2007-02-25T19:45:25Z")
You can make a copy of your worksheet and do Data-Filter-Advance Filter-Unique Records olny, then copy and paste the results (I wouldn’t do it with your main worksheet as it might mess up your data) ---
**Author:** ![Future\_Londonite](https://avatars.discourse-cdn.com/v4/letter/f/9dc877/32.png) [@Future\_Londonite](https://boards.straightdope.com/u/Future_Londonite)\ **Post date:** [February 25, 2007, 8:02pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/3 "2007-02-25T20:02:37Z")
Ok that seems to help somewhat - it does bring up another problem though. How can I copy/paste only the visible cells? The option you described just hides the other data but if I do a copy/paste it shows all data again (ie without it ever being filtered). ---
**Author:** ![scotandrsn](https://avatars.discourse-cdn.com/v4/letter/s/a4c791/32.png) [@scotandrsn](https://boards.straightdope.com/u/scotandrsn)\ **Post date:** [February 25, 2007, 8:09pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/4 "2007-02-25T20:09:32Z")
If I understand your problem correctly, you need a pivot table. You can specify in a pivot table that the first column shall be the account number, the second shall be the date. You will get a big cell with the account number on the left, then a list of dates from rows that had that account number, etc. ExcelXP has a Pivot Table Wizard to help you create one under the Data menu. Creating one is a little hard to describe in text without visuals, so I recommend the Wizard route first. ---
**Author:** ![F.U.Shakespeare](https://avatars.discourse-cdn.com/v4/letter/f/aca169/32.png) [@F.U.Shakespeare](https://boards.straightdope.com/u/F.U.Shakespeare)\ **Post date:** [February 25, 2007, 8:14pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/5 "2007-02-25T20:14:25Z")
I offer this as an alternative method: 1. Sort the data by account number 2. Create a new “indicator” column next to the account number column 3. In the first cell of the indicator column, write the following function: _=IF(X=Y,0,1)_ where X is the cell containing the adjacent account number, and Y is the cell containing the account number in the row below 1. Use “fill down” to replicate this function for all 6700 rows in the indicator column 2. Copy the indicator column, and use “paste special” to replace it with the “values only”. (This ensures that the numbers will not change when we re-sort the data). You now have a column that contains a 0 if the account number on that row was the same as the account number on the next row – i.e, all duplicates are flagged with a 0. 3. Sort the entire spreadsheet by the newly-pasted indicator column 4. Delete the rows with a 0 in the indicator column All account numbers are now represented once. (Hope I’ve got that right). Upon preview, I’m not sure I understand the issue with visible cells, so my method might not work with your data. ---
**Author:** ![Future\_Londonite](https://avatars.discourse-cdn.com/v4/letter/f/9dc877/32.png) [@Future\_Londonite](https://boards.straightdope.com/u/Future_Londonite)\ **Post date:** [February 25, 2007, 8:14pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/6 "2007-02-25T20:14:51Z")
[QUOTE=scotandrsn] If I understand your problem correctly, you need a pivot table. You can specify in a pivot table that the first column shall be the account number, the second shall be the date. You will get a big cell with the account number on the left, then a list of dates from rows that had that account number, etc. ExcelXP has a Pivot Table Wizard to help you create one under the Data menu. Creating one is a little hard to describe in text without visuals, so I recommend the Wizard route first. [/QUOTE] I don’t really see how a pivot table might help me. Basically what I have is this; Row 1 - ‘361001’ ‘Sales’ ‘Room Sales’ Row 2 - ‘361001’ ‘Sales’ ‘Room Sales’ Row 3 - ‘361001’ ‘Sales’ ‘Room Sales’ Row 4 - ‘361002’ ‘Sales’ ‘Bus Sales’ Row 5 - ‘361002’ ‘Sales’ ‘Bus Sales’ etc etc 6700+ rows where ’ ’ represents columns so I’m looking for a way to sort/arrange the dups out. The first option suggested hides the dupes but I can’t get it to copy/paste without the hidden rows coming back. If I do a pivot table I won’t be able to copy/paste either or am I seeing this wrong? ---
**Author:** ![Future\_Londonite](https://avatars.discourse-cdn.com/v4/letter/f/9dc877/32.png) [@Future\_Londonite](https://boards.straightdope.com/u/Future_Londonite)\ **Post date:** [February 25, 2007, 8:22pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/7 "2007-02-25T20:22:11Z")
[QUOTE=F. U. Shakespeare] I offer this as an alternative method: 1. Sort the data by account number 2. Create a new “indicator” column next to the account number column 3. In the first cell of the indicator column, write the following function: _=IF(X=Y,0,1)_ where X is the cell containing the adjacent account number, and Y is the cell containing the account number in the row below 1. Use “fill down” to replicate this function for all 6700 rows in the indicator column 2. Copy the indicator column, and use “paste special” to replace it with the “values only”. (This ensures that the numbers will not change when we re-sort the data). You now have a column that contains a 0 if the account number on that row was the same as the account number on the next row – i.e, all duplicates are flagged with a 0. 3. Sort the entire spreadsheet by the newly-pasted indicator column 4. Delete the rows with a 0 in the indicator column All account numbers are now represented once. (Hope I’ve got that right). Upon preview, I’m not sure I understand the issue with visible cells, so my method might not work with your data. [/QUOTE] Thanks - this method worked ---
**Author:** ![scotandrsn](https://avatars.discourse-cdn.com/v4/letter/s/a4c791/32.png) [@scotandrsn](https://boards.straightdope.com/u/scotandrsn)\ **Post date:** [February 25, 2007, 8:26pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/8 "2007-02-25T20:26:21Z")
[QUOTE=EuroMDguy] I don’t really see how a pivot table might help me. Basically what I have is this; Row 1 - ‘361001’ ‘Sales’ ‘Room Sales’ Row 2 - ‘361001’ ‘Sales’ ‘Room Sales’ Row 3 - ‘361001’ ‘Sales’ ‘Room Sales’ Row 4 - ‘361002’ ‘Sales’ ‘Bus Sales’ Row 5 - ‘361002’ ‘Sales’ ‘Bus Sales’ etc etc 6700+ rows where ’ ’ represents columns so I’m looking for a way to sort/arrange the dups out. The first option suggested hides the dupes but I can’t get it to copy/paste without the hidden rows coming back. If I do a pivot table I won’t be able to copy/paste either or am I seeing this wrong? [/QUOTE] I see you;ve found a solution, but I just checked with a little sample data, and had no trouble at all copying and pasting rows from a pivot table without duplicates. ---
**Author:** ![Future\_Londonite](https://avatars.discourse-cdn.com/v4/letter/f/9dc877/32.png) [@Future\_Londonite](https://boards.straightdope.com/u/Future_Londonite)\ **Post date:** [February 25, 2007, 8:36pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/9 "2007-02-25T20:36:44Z")
OK - one more ---
**Author:** ![Future\_Londonite](https://avatars.discourse-cdn.com/v4/letter/f/9dc877/32.png) [@Future\_Londonite](https://boards.straightdope.com/u/Future_Londonite)\ **Post date:** [February 25, 2007, 8:38pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/10 "2007-02-25T20:38:28Z")
question that is (and then I promise no more) I did a VLOOKUP now and somehow the data is some format that the formula doesn’t like (ie I get nothing but #N/A). If I do a F2 and Enter on my lookup array the formula works. The lookup array has 900 rows - is there a way for me to do a ‘mass’ F2/enter? Or do I have to F2/enter on each individually? Hope my question makes sense ---
**Author:** ![flex727](https://avatars.discourse-cdn.com/v4/letter/f/258eb7/32.png) [@flex727](https://boards.straightdope.com/u/flex727)\ **Post date:** [February 25, 2007, 8:40pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/11 "2007-02-25T20:40:45Z")
[QUOTE=EuroMDguy] Ok that seems to help somewhat - it does bring up another problem though. How can I copy/paste only the visible cells? The option you described just hides the other data but if I do a copy/paste it shows all data again (ie without it ever being filtered). [/QUOTE] Odd, I do this all the time and only the visible cells are copied. How are you performing the copy operation? In my case, after selecting the filter I want. I simply highlight the cells (click & drag with the mouse) and press ctrl-C. When I paste into another spreadsheet, only the visible cells go. ---
**Author:** ![Future\_Londonite](https://avatars.discourse-cdn.com/v4/letter/f/9dc877/32.png) [@Future\_Londonite](https://boards.straightdope.com/u/Future_Londonite)\ **Post date:** [February 25, 2007, 8:44pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/12 "2007-02-25T20:44:24Z")
[QUOTE=flex727] Odd, I do this all the time and only the visible cells are copied. How are you performing the copy operation? In my case, after selecting the filter I want. I simply highlight the cells (click & drag with the mouse) and press ctrl-C. When I paste into another spreadsheet, only the visible cells go. [/QUOTE] I might be confused then when copy/paste subtotalled data ---
**Author:** ![rowrrbazzle](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/rowrrbazzle/32/414_2.png) [@rowrrbazzle](https://boards.straightdope.com/u/rowrrbazzle)\ **Post date:** [February 25, 2007, 11:41pm UTC](https://boards.straightdope.com/t/hopefully-a-simple-excel-question/393632/13 "2007-02-25T23:41:32Z")
You could always ask the Excel guy: [Ask the EXCEL guy ... - Miscellaneous and Personal Stuff I Must Share - Straight Dope Message Board](http://boards.straightdope.com/sdmb/showthread.php?t=398567)