| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 119797 | 2011-08-10 08:52:00 | An excel formula that skips duplicate data | Ninjabear (2948) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1222309 | 2011-08-13 06:53:00 | Hopefully this link will work www.4shared.com I managed to get the file onto a computer with Excel on it. The conditional formatting doesn't work & it's to much of a learning curve to get it to work in a couple of minutes ;) Otherwise it's ok. Select the lot except the column titles etc & sort column A, all the duplicates are paired next to each other in numerical order & the total number of duplicate entries is in E7. I thought of doing it that way, as it is easier to compare the two entries, so one can be deleted. If the CF worked, it would show the top row of each duplicate pair as red. Do the select again & sort by the helper column to put it all back to how it was. Even the select & unselecting process is different. It appears that in Excel you cant select the whole sheet & unselect the top rows. More playing needed I think. |
Phil B (648) | ||
| 1222310 | 2011-08-13 11:28:00 | Hi Phil I had a look at your sheet and I think you mis-applied my formula. Per my post I recommended the formula was entered into row 2. Given you have entered the first formula into row 9, the formula needs to be entered like this: =IF(COUNTIF(A$9:A9,A9)=1,MAX(I$8:I8)+1,"") Sorry I didn't spell this out in my post that in the event it was entered into a row other than 2 that the row number needed to change for the first formula. Once amended, you should see it gives an incremental number to each unique item, and it doesn't require the data to be pre-sorted. Cheers, Andrew |
andrew93 (249) | ||
| 1222311 | 2011-08-14 04:08:00 | Hi Phil I had a look at your sheet and I think you mis-applied my formula. Per my post I recommended the formula was entered into row 2. Given you have entered the first formula into row 9, the formula needs to be entered like this: =IF(COUNTIF(A$9:A9,A9)=1,MAX(I$8:I8)+1,"") Sorry I didn't spell this out in my post that in the event it was entered into a row other than 2 that the row number needed to change for the first formula. Once amended, you should see it gives an incremental number to each unique item, and it doesn't require the data to be pre-sorted. Cheers, Andrew Aha. I see that now.. He/She's now got 2 different solutions to play with Cheers |
Phil B (648) | ||
| 1 2 | |||||