Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 94144 times)

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4394
  • Total likes: 2
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #135 on: May 22, 2014, 10:36:59 PM »
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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2810
  • Total likes: 153
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #136 on: May 23, 2014, 08:51:15 AM »
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.

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4394
  • Total likes: 2
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #137 on: May 23, 2014, 12:51:32 PM »
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

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #138 on: July 09, 2014, 10:26:49 PM »
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

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5051
  • Total likes: 14
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #139 on: July 09, 2014, 10:51:52 PM »
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

Offline EJB

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2012
  • Posts: 5070
  • Total likes: 148
  • DansDeals.com Hat Tips 15
    • View Profile
Re: Excel Problem
« Reply #140 on: July 09, 2014, 10:59:51 PM »
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

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #141 on: July 09, 2014, 11:11:06 PM »
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

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5051
  • Total likes: 14
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #142 on: July 09, 2014, 11:21:40 PM »
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

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2592
  • Total likes: 29
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #143 on: July 09, 2014, 11:24:06 PM »
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.

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #144 on: July 09, 2014, 11:39:19 PM »
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

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #145 on: July 09, 2014, 11:41:00 PM »
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

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5051
  • Total likes: 14
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #146 on: July 09, 2014, 11:43:01 PM »
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?

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #147 on: July 09, 2014, 11:45:26 PM »
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

Offline username

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jul 2008
  • Posts: 4059
  • Total likes: 285
  • DansDeals.com Hat Tips 10
  • Gender: Male
    • View Profile
  • Location: C Heights
  • Programs: ┌──────────────────┐ │───> Ữ₴ΣЯИΛΜӘ <─── │ └──────────────────┘
Re: Excel Problem
« Reply #148 on: July 09, 2014, 11:50:54 PM »
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
^^^

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 475
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #149 on: July 09, 2014, 11:58:21 PM »
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