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,
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)
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).
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.
I offer this as an alternative method:
Sort the data by account number
Create a new “indicator” column next to the account number column
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
Use “fill down” to replicate this function for all 6700 rows in the indicator column
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.
Sort the entire spreadsheet by the newly-pasted indicator column
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.
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?
Thanks - this method worked
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.
OK - one more
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
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.
I might be confused then when copy/paste subtotalled data
You could always ask the Excel guy: Ask the EXCEL guy ... - Miscellaneous and Personal Stuff I Must Share - Straight Dope Message Board