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 291417 times)

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #300 on: June 02, 2015, 06:22:19 PM »
Excel has a great Pivot Chart feature built in. Should be exactly what you're looking for.
Thanks, no idea why I never saw the pivot chart option.
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5116
  • Total likes: 2238
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #301 on: June 02, 2015, 07:39:05 PM »
Thanks, no idea why I never saw the pivot chart option.
Live & Learn
["-"]

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #302 on: June 09, 2015, 05:36:21 PM »
Is there a way to paste a formula into multiple cells that already have data. For Example - A1:A120 has random numbers in each cell I want to add a formula to each cell in addition to the number in the cell currently.
TIA
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3855
  • Total likes: 70
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #303 on: June 09, 2015, 05:42:25 PM »
Is there a way to paste a formula into multiple cells that already have data. For Example - A1:A120 has random numbers in each cell I want to add a formula to each cell in addition to the number in the cell currently.
TIA
Say your formula is 1+1. In B1 enter =A1+1+1 and drag down

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #304 on: June 09, 2015, 05:55:47 PM »
Say your formula is 1+1. In B1 enter =A1+1+1 and drag down
Thanks
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #305 on: July 02, 2015, 02:05:30 PM »
Have a table, columns are the names and rows are months.

How can I convert this to a list where that will have all the months in column A and the name in B? So it will list


Jan sam transaction
Jan jack transaction
feb chaim transaction
feb shemerel transaction

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 4077
  • Total likes: 835
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #306 on: July 02, 2015, 03:19:06 PM »
Have a table, columns are the names and rows are months.

How can I convert this to a list where that will have all the months in column A and the name in B? So it will list


Jan sam transaction
Jan jack transaction
feb chaim transaction
feb shemerel transaction
Easiest thing to do would be to create the first 2 columns yourself by copy and pasting, then use INDEX/MATCH in the 3rd column to pull in the transaction for each combination.

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25499
  • Total likes: 723
  • DansDeals.com Hat Tips 15
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #307 on: July 21, 2015, 10:22:23 PM »
How would I sort a pivot table by criteria that is not in the pivot table itself, but IS in the source data?

Looking to sort first column by date, but date is not included in pivot table, but it in source data.
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline shoobi

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 640
  • Total likes: 5
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #308 on: July 22, 2015, 11:56:32 AM »
Try adding it to the pivot table in a non-altering way? i.e. add the date field to the "filter" section of the pivot table and keep it selected to "all"

ETA: yeah doesn't seem like a solution
« Last Edit: July 22, 2015, 12:06:05 PM by shoobi »

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25499
  • Total likes: 723
  • DansDeals.com Hat Tips 15
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #309 on: July 22, 2015, 12:02:18 PM »
Try adding it to the pivot table in a non-altering way? i.e. add the date field to the "filter" section of the pivot table and keep it selected to "all"

Filter would only do that, "filter."

I need it sorted by a date that's present in the source data.

From my searching, I'm not sure it can be done.
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline EJB

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2012
  • Posts: 5410
  • Total likes: 254
  • DansDeals.com Hat Tips 15
    • View Profile
Re: Excel Problem
« Reply #310 on: July 22, 2015, 02:00:58 PM »
Filter would only do that, "filter."

I need it sorted by a date that's present in the source data.

From my searching, I'm not sure it can be done.

Don't think it's possible without VBA or presorting data, unless you are ok adding a hidden or invisible column.

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25499
  • Total likes: 723
  • DansDeals.com Hat Tips 15
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #311 on: July 22, 2015, 03:31:14 PM »
That's what I figured.

What do you mean by presorting data or adding hidden column?
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline Ergel

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Jun 2010
  • Posts: 13070
  • Total likes: 905
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Problem
« Reply #312 on: July 22, 2015, 05:57:28 PM »
That's what I figured.

What do you mean by presorting data or adding hidden column?
Presorting data, he means sorting the source table
Hidden column would mean adding an extra column with the date in your pivot table and sorting based on that. However, being that the column is not present in your pivot table, he assumes that you don't want to display the date in your pivot table. As such, he is recommending hiding that column.
Life isn't about checking the boxes. Nobody cares.

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #313 on: July 23, 2015, 04:53:12 PM »
That's what I figured.

What do you mean by presorting data or adding hidden column?
You should be able to sort pivot tbl by source data. Maybe right click and see if the option is there

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5116
  • Total likes: 2238
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #314 on: July 23, 2015, 11:20:25 PM »
Does anyone Here know the "Solver" Password?
["-"]

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #315 on: July 23, 2015, 11:25:00 PM »
To confirm. If I have a sheet with rows of info for ex. A name b address c transaction date etc. if I select all the data and hit remove duplicates it will only remove a row if all the data in the row matches to another row, is this correct?
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5082
  • Total likes: 20
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #316 on: July 24, 2015, 12:03:15 AM »
To confirm. If I have a sheet with rows of info for ex. A name b address c transaction date etc. if I select all the data and hit remove duplicates it will only remove a row if all the data in the row matches to another row, is this correct?

It will give you the option to choose which columns need to match to be considered duplicates. Default is that all the columns need to match, like you are saying.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 775
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #317 on: July 24, 2015, 12:10:33 AM »
It will give you the option to choose which columns need to match to be considered duplicates. Default is that all the columns need to match, like you are saying.
Thanks.
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline yesitsme

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2014
  • Posts: 5116
  • Total likes: 2238
  • DansDeals.com Hat Tips 4
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #318 on: July 24, 2015, 12:59:38 AM »
To create a sheet for each month in VBA
Code: [Select]
Sub createbooks()
Dim x As Integer
Dim i As Integer
 
For i = 1 To 11
If i = ActiveWorkbook.Sheets.Count Then
     ActiveWorkbook.Sheets.Add
    End If
Next
 
For x = 1 To ActiveWorkbook.Sheets.Count
   Sheets(x).Name = MonthName(x)
Next
End Sub
["-"]

Offline PTU

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Nov 2014
  • Posts: 289
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #319 on: July 24, 2015, 09:25:37 AM »
To confirm. If I have a sheet with rows of info for ex. A name b address c transaction date etc. if I select all the data and hit remove duplicates it will only remove a row if all the data in the row matches to another row, is this correct?
When in doubt, always do your work on a duplicate tab. Always.