Excel Help and Problems

[smurf]

Sometimes =A1&B1 doesnt work (possibly when the cells are formatted diferently) and I've found concatenate to work mire consistently

and it's easier to combine more then 2 cells that way.
Regardless I don't like the way either method handles blank cells so I use a borrowed UDF

[skyguy918]

and it's easier to combine more then 2 cells that way.
Regardless I don't like the way either method handles blank cells so I use a borrowed UDF

Disagree on the first point. In what way is it easier? You have to put a comma in between each reference in CONCATENATE anyway, just sub in an ampersand and drop the CONCATENATE function.

And what's the issue with blank cells? A blank cell has the value "", which is what it puts in the cell value when you use either method. UDF's are always a last resort for me. They're notoriously inefficient relative to built in functions.

[smurf]

Disagree on the first point. In what way is it easier? You have to put a comma in between each reference in CONCATENATE anyway, just sub in an ampersand and drop the CONCATENATE function.

And what's the issue with blank cells? A blank cell has the value "", which is what it puts in the cell value when you use either method. UDF's are always a last resort for me. They're notoriously inefficient relative to built in functions.

you're right,I forgot how to use concatenate. I haven't used it in a while.

My issue with blank cells is that i often need to concatenate a range of cells with a delimiter between each one. The simple way to do it is to store the delimiter in a cell and reference it between each cell in the concatenate. However with a blank cell I'll then have 2 consecutive delimiter.
So I use udf

[unavailable]

hi
when removing duplicates is there an option that it should ask you what duplicate to remove/keep. or at least show what duplicate was removed?

tia

[bubbles]

hi
when removing duplicates is there an option that it should ask you what duplicate to remove/keep. or at least show what duplicate was removed?

tia

if they are duplicates what's the difference? you can highlight duplicates instead of deleting right away under condition formatting > highlight cells rules > duplicate values.

If you let us know what you are trying to accomplish maybe can be of more help

[EJB]

hi
when removing duplicates is there an option that it should ask you what duplicate to remove/keep. or at least show what duplicate was removed?

tia

No direct option. If you want to delete all duplicates besides a specific set, create an indicator to flag those in another column and delete based on that column. Loads
Of alternatives using vba

[unavailable]

if they are duplicates what's the difference? you can highlight duplicates instead of deleting right away under condition formatting > highlight cells rules > duplicate values.

thanks used it
have a list of names and phone #'s  wanted to make sure no number has 2 names (by error)

No direct option. If you want to delete all duplicates besides a specific set, create an indicator to flag those in another column and delete based on that column. Loads
Of alternatives using vba

maybe, but I have no idea what's vba

thanks anyway

[bubbles]

thanks used it
have a list of names and phone #'s  wanted to make sure no number has 2 names (by error)
maybe, but I have no idea what's vba

thanks anyway

You can delete values that are only duplicate in two columns. So it won't delete the number if the names are mismatched

[yitzf]

hi
when removing duplicates is there an option that it should ask you what duplicate to remove/keep. or at least show what duplicate was removed?

tia

You could make a copy of your data, and then remove duplicated, and compare the 2 versions to see which oned it took out.
It might be easier if you number the rows first, so then you can see which rows were taken out.

[unavailable]

You can delete values that are only duplicate in two columns. So it won't delete the number if the names are mismatched

yes but I wanted to see those names

[unavailable]

You could make a copy of your data, and then remove duplicated, and compare the 2 versions to see which oned it took out.
It might be easier if you number the rows first, so then you can see which rows were taken out.

Right, but we're talking 2000 names

[bubbles]

yes but I wanted to see those names

I'm apparently not following. You have a list of names in column A and numbers in column B. You can highlight duplicates to see which values are going to be deleted. You can highlight duplicate values in column A alone and column B alone to find mismatched names. You can delete duplicates by two columns to make sure you don't delete any mismatched names. What else you need to do?

[unavailable]

I'm apparently not following. You have a list of names in column A and numbers in column B. You can highlight duplicates to see which values are going to be deleted. You can highlight duplicate values in column A alone and column B alone to find mismatched names. You can delete duplicates by two columns to make sure you don't delete any mismatched names. What else you need to do?

I used your idea

[username]

Create a column concecating the name and phone number. Then on that column remove duplicates and sort asceding, then scroll down and see if any numbers have more than one name on it

[unavailable]

Create a column concecating the name and phone number. Then on that column remove duplicates and sort asceding, then scroll down and see if any numbers have more than one name on it

I don't follow. If I remove duplicates I won't be able to see if the numbers have 2 names again I'm talking 2k names for now