Excel question

how do you make it so it highlights any duplicate entries in red?

feel free to tell me how or to make shite jokes, either are fine

Conditional formatting and duplicate values

4 Likes

Select the cells then select conditional formatting in the home tab, then highlight cells rules and duplicate values.

ok great

but just for fun, imagine if someone didn’t know the difference between excel and google spreadsheets, what would they do then?

3 Likes

:rofl:

1 Like

Copy and paste it into excel

2 Likes
1 Like

ah fuck it, i give up

I had to do this and couldn’t find a way. Ended up making a formula saying if cell is a duplicate return true, else return false, then ran a conditional format on true

yeah i cba

i’m not paid to do spreadsheet bollocks, fuck em

1 Like

Does it need to be self maintaining or a one off, if the later just add a column, do a countif by each value to see how many cells in the column match that value then filter on that column for values over 1 and manually apply the highlight

wat

If you had a list of values in column A in column B in the first row you want to check, say cell B2 put the formula =countif(a:a,a2) and it will count all the values in column a that match a2. Copy this down for all the values, then add a filter to column b and deselect 1 giving you all the duplicates

so you have to do a different value for each cell in a list?

The formula will be relative (unless you fix it with $ before the column and/or row) so when you copy the formula down it will automatically know to refer the the cell on the left

1 Like

Hi.

I have a spreadsheet with a list of thousands of postcodes, with each postcode having a person’s name in the field next to it.

I want to extract a list of which postcode areas (M21, M16 etc) are assigned to which person. Each area only has one person allocated to it. I want to create a VLOOKUP so on a new spreadsheet if I type in M21 it will find out which person that area belongs to and tell me. Is there an easy way of doing this other than manually going through them?

This is pretty much what VLOOKUP is for - unless I’m missing something

L5 formula is =IFERROR(VLOOKUP($K$5,B:C,2,FALSE),“Not Found”)

You can remove the iferror bit but makes it looks nicer if they put in an area without a person

1 Like

Are they full postcodes in normal postcode format, e.g. M21 1AA, etc? If so, could you do text to columns, separate by space so you’ve got a column just with M21, M22, etc, then vlookup.

Edit: there’s probably a more efficient way to do this just with a formula rather than split the postcode.

:grinning:

The postcodes I have are all full postcodes though, I want it to just pick up the first 3 (or 4) characters

Do they have a space in the middle?