Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

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

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 836
  • Total likes: 26
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #500 on: January 23, 2016, 07:34:11 PM »
Yes. organize it as a table and it should to it automatically.
So have each reward system is own table and column E as the sum of each table?
I don't sin, I give myself opportunities to repent.

Offline lechatchileh ariber

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Mar 2013
  • Posts: 836
  • Total likes: 26
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #501 on: January 23, 2016, 10:09:42 PM »
Yes. organize it as a table and it should to it automatically.
I didn't really get what you meant so I did this:
=sumif(A:A, "HERTZ", B:B)
Seems to be working, but I have to fill in column A.
I don't sin, I give myself opportunities to repent.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 772
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #502 on: January 25, 2016, 10:08:03 AM »
Anyway to have a vlookup return "0" for no match instead of "false"?
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline myb821

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Mar 2011
  • Posts: 7570
  • Total likes: 28
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #503 on: January 25, 2016, 10:34:27 AM »
Anyway to have a vlookup return "0" for no match instead of "false"?
=iferror(vlookup(....),0)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #504 on: January 25, 2016, 10:34:46 AM »
Anyway to have a vlookup return "0" for no match instead of "false"?
VLOOKUP doesn't return FALSE for no match, it returns #N/A. If you want to return 0 for no match you can surround your VLOOKUP with IFERROR:
=IFERROR(VLOOKUP(...),0)

ETA
=iferror(vlookup(....),0)
Beat me to it by a minute.

Also, technically speaking this obscures any other errors, so if you want to be really careful you can use =IF(ISNA(VLOOKUP(...),0,VLOOKUP(...))

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 772
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #505 on: January 25, 2016, 10:37:00 AM »
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 772
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #506 on: January 25, 2016, 10:37:26 AM »
VLOOKUP doesn't return FALSE for no match, it returns #N/A. If you want to return 0 for no match you can surround your VLOOKUP with IFERROR:
=IFERROR(VLOOKUP(...),0)

ETABeat me to it by a minute.

Also, technically speaking this obscures any other errors, so if you want to be really careful you can use =IF(ISNA(VLOOKUP(...),0,VLOOKUP(...))
Correct. I meant n/a. Thanks.
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #507 on: January 26, 2016, 12:36:05 PM »
Anyone know a Macro to Copy one Column and paste that column every other column for the next 500 columns??

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #508 on: January 26, 2016, 12:39:31 PM »
Anyone know a Macro to Copy one Column and paste that column every other column for the next 500 columns??
Could just do a formula and drag

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #509 on: January 26, 2016, 12:41:21 PM »
Its not a formula just text..

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #510 on: January 26, 2016, 12:43:50 PM »
Its not a formula just text..
Yes =a1 will return the text in a1

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #511 on: January 26, 2016, 12:47:29 PM »
Good idea thank you

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #512 on: January 26, 2016, 12:49:13 PM »
There is still a issue, I need the formatting to come along, when you use the Formula it does not pull that...

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3761
  • Total likes: 70
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #513 on: January 26, 2016, 12:50:50 PM »
There is still a issue, I need the formatting to come along, when you use the Formula it does not pull that...
Use format painter (the paintbrush near the paste button on the home ribbon)

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #514 on: January 26, 2016, 01:02:09 PM »
its thousands of columns, a macro would make my life a lot easier...

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3761
  • Total likes: 70
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #515 on: January 26, 2016, 01:05:07 PM »
its thousands of columns, a macro would make my life a lot easier...

Meh. A double click here and there and you're done. I work on spreadsheets with hundreds of thousands of rows etc. Doesn't take long when you know your way around

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #516 on: January 26, 2016, 01:37:36 PM »
Don't bother with the =A1, just highlight the column with the text, and drag the fill cursor all the way to the right till you hit column SF (that's the 500th column). It'll take a few seconds holding it there while it scrolls, but it also copies the formatting.

ETA: Even better solution. Type SF1 into the Name Box (that's the thing to the left of the formula bar, which will navigate you to cell SF1. Type 1, or anything else into that cell. Click CTRL+HOME and select the data you want to copy. With the source data selected, hit CTRL+SHIFT+(right arrow). Then hit CTRL+R.
« Last Edit: January 26, 2016, 01:45:04 PM by skyguy918 »

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #517 on: January 26, 2016, 02:37:37 PM »
Don't bother with the =A1, just highlight the column with the text, and drag the fill cursor all the way to the right till you hit column SF (that's the 500th column). It'll take a few seconds holding it there while it scrolls, but it also copies the formatting.

ETA: Even better solution. Type SF1 into the Name Box (that's the thing to the left of the formula bar, which will navigate you to cell SF1. Type 1, or anything else into that cell. Click CTRL+HOME and select the data you want to copy. With the source data selected, hit CTRL+SHIFT+(right arrow). Then hit CTRL+R.
He wanted every other column. Needs to do two first, one empty, select both and drag.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #518 on: January 26, 2016, 02:48:59 PM »
He wanted every other column. Needs to do two first, one empty, select both and drag.
Lol, I read it that way first, then somehow convinced myself he meant all of the other columns.

Either way, the name box trick is still helpful because it allows you to mark the spot you want to stop instead of sitting there waiting till your drag reaches 500 columns. This way you can select the first 2 columns, CTRL+C, CTRL+SHIFT+(right arrow), CTRL+V.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2623
  • Total likes: 52
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #519 on: January 26, 2016, 06:49:06 PM »
Anyone know a Macro to Copy one Column and paste that column every other column for the next 500 columns??

Here you go

Code: [Select]
Sub copy500()

Dim data As Range
Dim startCol As Range

Set data = Application.InputBox(Prompt:="Select anywhere in the column you wish to copy", Type:=8)

If data Is Nothing Then Exit Sub

Set startCol = Application.InputBox(Prompt:="Select anywhere in the first column you want to paste into", Type:=8)

If startCol Is Nothing Then Exit Sub

For i = startCol.Column To startCol.Column + 999
    data.EntireColumn.Copy Columns(i)
    i = i + 1
Next i

End Sub
« Last Edit: January 26, 2016, 08:22:20 PM by yitzf »