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

wat

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

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?

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

Yep, all of them. I just want it to reduce down to postcode district, which is just the first 2/3/4 characters. Everything before the space, basically.

Thanks, but idk what text to columns is?

Just splits a column into two or more, based on rules you set. Select the column, go to data, then select text to columns. You can then separate them either by a fixed width (e.g. after 4 characters) or by something like space, which I think is what you want. Wherever there’s a space, it will put the data into different columns, so M21 1AA in column A would go M21 in column A and 1AA in column B.

s_a_d is probably saving you a step though, go with what he says.

why the hell am I watching this thread

i’ve been hacked

1 Like

Okay, thanks.

I think this data might fuck that up though, as there are some legacy postcode districts that have two names attached to them.

Also, for example, if I type in BB1 in the new spreadsheet and send it off to look that up, how will it determine whether to look for BB1 or BB10?

That would be beneficial, but how would I specify where to stop if there are postcodes that vary from E1 to M16 to PO34, for example?