excel formula

tom_tarbucket

Hey all,

Looking for an excel formula or a way of doing this

Have a list of 1300 or so e-mail addresses in column A.
I need to upload these e-mail addresses to a system.
However, roughly 200 of these e-mail addresses already exist in the system, I have this list. I do not want to upload the 200 that already exist.

So would have one column with 1300 entires, another column with 200 entries.

Whats the easiest way to compare the 2 columns and highlight or even take away the ones which already exist?

i.e end goal, I dont want to upload the ones that already exist and therefore need them taken away.

Jimbob1977

tom_tarbucket wrote: »

Hey all,

Looking for an excel formula or a way of doing this

Have a list of 1300 or so e-mail addresses in column A.
I need to upload these e-mail addresses to a system.
However, roughly 200 of these e-mail addresses already exist in the system, I have this list. I do not want to upload the 200 that already exist.

So would have one column with 1300 entires, another column with 200 entries.

Whats the easiest way to compare the 2 columns and highlight or even take away the ones which already exist?

i.e end goal, I dont want to upload the ones that already exist and therefore need them taken away.

There is a Duplicate function in Excel

If Column A is your 1300 (with 200 dupes)

If Column B is your 200 system entries

Place them all in a single column (say Column A)

Data tab on Ribbon -> Remove Duplicates

The duplicates will be removed


Or else.... If you want to see the duplicates highlighted in a different colour first....

Once the column is selected:

Select the entire column.
Go to the Home tab of the Ribbon.
Open the Conditional Formatting drop-down menu.
Select “Highlight Cell Rules”.
Then select “Duplicate Values…”
That will open the Duplicate Values Window.
Choose the formatting you want to apply and click OK.

ajc100

Use a Ref Table & VLOOKUP.

Column A = 1300 email addresses
Column B = '=VLOOKUP(A2,Table1,2,FALSE)

Table1 included 200 emails and additional column with 'N' or any value.

Filter on Column B to get the unique values - N\A.
Untitled.jpg

tom_tarbucket

Thanks all

NOt too usre on VLOOKUP stuff.

Is there any way to just put the people that already exist to another column

Column A = 1300 people
Column B = 200 people mixed in with 1300 people
Column C = formula done to only show the 1300 minus the 200 people and if possible highlight who the 200 are.

Jimbob1977

tom_tarbucket wrote: »

Thanks all

NOt too usre on VLOOKUP stuff.

Is there any way to just put the people that already exist to another column

Column A = 1300 people
Column B = 200 people mixed in with 1300 people
Column C = formula done to only show the 1300 minus the 200 people and if possible highlight who the 200 are.

If it's formula driven, you would have to use the vlookup. I can't think of another way.

If it's visual, you could

i) use the Conditional Formatting to colour code the duplicates in Column A. Column A would actually need to be 1500 entries (1300 list plus 200 system).

Paste all the duplicates into Column B, ensuring that just one legitimate entry remains.


ii) anything that isn't colour coded as a duplicate in Column A is not a duplicate and unique/new. Paste the uncoloured entries from Column A into Column C.

Then you should have

A your total list of 1300
B 200
C 1100

tobottherobot

Put them all in the same column. Highlight the column using the 'conditional formatting' tab and select duplicates.

Sort the column by the colour you've highlighted the dupe's and delete. Very quick to do!

Jimbob1977

tobottherobot wrote: »

Put them all in the same column. Highlight the column using the 'conditional formatting' tab and select duplicates.

Sort the column by the colour you've highlighted the dupe's and delete. Very quick to do!

You'd have to ensure you didn't delete all the coloured items as they would contain both the original and the duplicate entry. You'd just have to delete half of them (i.e. the second dupes only)

tom_tarbucket

Jimbob1977 wrote: »

You'd have to ensure you didn't delete all the coloured items as they would contain both the original and the duplicate entry. You'd just have to delete half of them (i.e. the second dupes only)

Any better way to do it ?

Jimbob1977

tom_tarbucket wrote: »

Any better way to do it ?

For the colour-coded duplicates identified by Conditional Formatting, you could actually use the previously mentioned "Remove Duplicates" functionality on them now. Then you'd have the list of 'already on the system' just once.

So, in summary, follow these steps:

1. Set up a single column with the 1,500 entries (total current list of 1,300 plus existing system list of 200).

2. Use Conditional Formatting -> Duplicates on the column and maybe highlight all the duplicates in red font.

3. Anything that isn't coloured red is a unique member of the 1,100 set. It is not a duplicate. You have your 1,100 brand new entries for upload. Set them aside.

4. There should be 400 coloured entries - 200 on the total list and their 200 mirror images on the system list. Set them aside. They are effectively the same thing. They appear twice. They are the items already in the system * 2.

Use 'Data -> Remove Duplicates'. You will get a message like 'Excel has removed 200 duplicates'.

Your list of 400 entries will be cut precisely in half. Now you have your list of 200 entries that were already in the system and dont need to be uploaded again.

Follow the steps and you will have your answer.

Step 3 - unique new entries (1,100)

Step 4 - the items already noted in the system (200).

If you could read up on v-lookups, it would be just as quick.

tom_tarbucket

Jimbob1977 wrote: »

For the colour-coded duplicates identified by Conditional Formatting, you could actually use the previously mentioned "Remove Duplicates" functionality on them now. Then you'd have the list of 'already on the system' just once.

So, in summary, follow these steps:

1. Set up a single column with the 1,500 entries (total current list of 1,300 plus existing system list of 200).

2. Use Conditional Formatting -> Duplicates on the column and maybe highlight all the duplicates in red font.

3. Anything that isn't coloured red is a unique member of the 1,100 set. It is not a duplicate. You have your 1,100 brand new entries for upload. Set them aside.

4. There should be 400 coloured entries - 200 on the total list and their 200 mirror images on the system list. Set them aside. They are effectively the same thing. They appear twice. They are the items already in the system * 2.

Use 'Data -> Remove Duplicates'. You will get a message like 'Excel has removed 200 duplicates'.

Your list of 400 entries will be cut precisely in half. Now you have your list of 200 entries that were already in the system and dont need to be uploaded again.

Follow the steps and you will have your answer.

Step 3 - unique new entries (1,100)

Step 4 - the items already noted in the system (200).

If you could read up on v-lookups, it would be just as quick.

Hey JImbob, thanks for helpful reply.

I have done the first 2 steps, everything in one column A.... and duplicates highlighted in red.

How do I get the red stuff moved to a new column B and then remove the spaces in A left by moving them to the new column

tom_tarbucket

tom_tarbucket wrote: »

Hey JImbob, thanks for helpful reply.

I have done the first 2 steps, everything in one column A.... and duplicates highlighted in red.

How do I get the red stuff moved to a new column B and then remove the spaces in A left by moving them to the new column

D'oh, ignore, I can sort by cell colour

tom_tarbucket

actually , one more q. if you have a big long excel list i.e a column with a few hundred entries and some of the cells are not populated,

e.g a1-a26 has all cells populated except for a3, a6, a 15, a17, a 24

how do you remove the white spaces to have everything grouped all together

raxy

Heres a site I use for excel help. I'm sure you'll get the best way of doing what you want from here.
https://www.eileenslounge.com/

raxy

tom_tarbucket wrote: »

actually , one more q. if you have a big long excel list i.e a column with a few hundred entries and some of the cells are not populated,

e.g a1-a26 has all cells populated except for a3, a6, a 15, a17, a 24

how do you remove the white spaces to have everything grouped all together

sorting the column would put the blanks at the end

Jimbob1977

tom_tarbucket wrote: »

actually , one more q. if you have a big long excel list i.e a column with a few hundred entries and some of the cells are not populated,

e.g a1-a26 has all cells populated except for a3, a6, a 15, a17, a 24

how do you remove the white spaces to have everything grouped all together

A data -> sort will bring similar items together very easily

The cells with content will come together

The blank cells will come together.

So if you started with a string of 28 cells (25 populated, 3 blank)... The Data -> Sort By .... will bring the 25 populated cells together into a block. The three blanks will sit together at the top or the bottom and become irrelevant.

As raxy said, it's great to familiarise yourself with everyday Excel, which I would consider:

Data Sorting
V-lookups
Conditional Formatting
Pivot tables
"If" arguments

Excel is a phenomenal tool. I understand only about 20% of its functionality, but it gets me by. The Help function in Excel is also easy to follow

tom_tarbucket

Thanks. I agree excel can do some amount of stuff.

Last q what is difference between

1. Highlight column > data > remove duplicates
2. Conditional Formatting -> highlight cell rules > Duplicates values

raxy

remove duplicates will remove the duplicates, conditional formatting will only highlight them.

tom_tarbucket

raxy wrote: »

remove duplicates will remove the duplicates, conditional formatting will only highlight them.

when you put it like that, its a retarded question I asked

raxy

Had a search myself on this. If your e-mail list is in column A & the list already in you database is in column B then enter =IF(ISNA(VLOOKUP(A1,$B$1:$B$1500,1,FALSE)),A1,"") into column C, than copy the formula down in column C for the length of the list.
That will copy the e-mail from column A into C as long as it is not found in column B. It will leave a blank if the e-mail is in column C so sort the column after.

The formula could be improved to make it easier. Column C could be turned into a dynamic named range which could be referenced in the formula so its not checking too many cells for data.

daheff

maybe a quick pivot would be the best way. But depends on how good you are at excel


If you highlight the data (put a header on the data-eg email). Go to Insert on top ribbon and Pivot table.

Then put Email into values (set as count) and rows

then filter out anything with 1 or less counts.

tom_tarbucket

raxy wrote: »

Had a search myself on this. If your e-mail list is in column A & the list already in you database is in column B then enter =IF(ISNA(VLOOKUP(A1,$B$1:$B$1500,1,FALSE)),A1,"") into column C, than copy the formula down in column C for the length of the list.
That will copy the e-mail from column A into C as long as it is not found in column B. It will leave a blank if the e-mail is in column C so sort the column after.

The formula could be improved to make it easier. Column C could be turned into a dynamic named range which could be referenced in the formula so its not checking too many cells for data.

mm, did this as you said

column A has all the e-mail addresses
column B only has the ones already added.
entered your formula to column C - and when applied, it just lists exactly what is in column A.

raxy

If you want to attach your file I can take a look for you but that formula is doing what you want on a sheet I made. I've attached it here

Neon_Lights

Just saying, I think it's time to Learn SQL lol

KAGY

Here's another way without using vlookup.
I'm on my phone so excuse the shorthand

All email addresses into one column , let's say A, data starting at A2,
B2 - blank
B3 =if(A3=A$2:A2,"DUP","unique") press Ctrl shift enter to add this as an array formula, and drag down

Sort by column B and delete all rows with DUP