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

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1785
  • Total likes: 157
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Help and Problems
« Reply #1340 on: December 18, 2019, 10:40:46 AM »
I usually select the column and click Data > Remove Duplicates
Ah, that is easier.

My use case usually needs it to update dynamically, where the formula works really well.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12855
  • Total likes: 3302
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1341 on: December 18, 2019, 10:42:11 AM »
Ah, that is easier.

My use case usually needs it to update dynamically, where the formula works really well.
Right! But if you don't need dynamic this is easier than doing a formula and then paste-values.

It's one thing that I wish google sheets did btw :)
Workflowy. You won't know what you're missing until you try it.

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1785
  • Total likes: 157
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Help and Problems
« Reply #1342 on: December 18, 2019, 10:55:58 AM »
Right! But if you don't need dynamic this is easier than doing a formula and then paste-values.

It's one thing that I wish google sheets did btw :)
Hmm
Google added remove duplicates this year
https://gsuiteupdates.googleblog.com/2019/05/new-sheets-features-may.html?m=1

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12855
  • Total likes: 3302
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1343 on: December 18, 2019, 10:57:14 AM »
Hmm
Google added remove duplicates this year
https://gsuiteupdates.googleblog.com/2019/05/new-sheets-features-may.html?m=1
Oooh, nice, I must have missed that. Thanks!!
Workflowy. You won't know what you're missing until you try it.

Offline Der Deutsche Jude

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Jan 2016
  • Posts: 934
  • Total likes: 46
  • DansDeals.com Hat Tips 4
    • View Profile
  • Location: Brooklyn, NY
  • Programs: AA, CA, FAA, GA, NA, OA, WA
Re: Excel Help and Problems
« Reply #1344 on: January 03, 2020, 11:20:39 AM »
Any interest in having a VBA thread?

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4375
  • Total likes: 1315
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1345 on: January 07, 2020, 07:55:37 PM »
Any interest in having a VBA thread?

Me!
If anyone here is a VBA expert, I've been struggling with this issue for a while now. It's really impacting my work.
Any help would be appreciated.


Offline EJB

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2012
  • Posts: 5409
  • Total likes: 254
  • DansDeals.com Hat Tips 15
    • View Profile
Re: Excel Help and Problems
« Reply #1346 on: January 07, 2020, 10:28:00 PM »
Me!
If anyone here is a VBA expert, I've been struggling with this issue for a while now. It's really impacting my work.
Any help would be appreciated.

Are you also opening many workbooks? Perhaps they are still in memory and you need to flush them out once finished?

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4375
  • Total likes: 1315
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1347 on: January 08, 2020, 12:30:20 AM »
Are you also opening many workbooks? Perhaps they are still in memory and you need to flush them out once finished?
If you follow the code you'll see that I close each workbook after importing the sheet.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3809
  • Total likes: 822
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1348 on: January 08, 2020, 10:20:31 AM »
I get the memory error sometimes also, when there's clearly no actual memory issue. But it's infrequent enough that I don't bother trying to fix it.

Offline Der Deutsche Jude

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Jan 2016
  • Posts: 934
  • Total likes: 46
  • DansDeals.com Hat Tips 4
    • View Profile
  • Location: Brooklyn, NY
  • Programs: AA, CA, FAA, GA, NA, OA, WA
Re: Excel Help and Problems
« Reply #1349 on: January 11, 2020, 06:25:40 PM »
Me!
If anyone here is a VBA expert, I've been struggling with this issue for a while now. It's really impacting my work.
Any help would be appreciated.
Would it help if you slow down the code? I've seen something similar from John Walkenbach where his code was running faster than the shell object was able to process, so he had a do until loop check every second if it was ready to proceed.

Also just curious why you first used the sheets collection and then the worksheets collection
« Last Edit: January 11, 2020, 06:30:16 PM by Der Deutsche Jude »

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4375
  • Total likes: 1315
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1350 on: January 12, 2020, 12:34:30 PM »
Would it help if you slow down the code? I've seen something similar from John Walkenbach where his code was running faster than the shell object was able to process, so he had a do until loop check every second if it was ready to proceed.

Also just curious why you first used the sheets collection and then the worksheets collection

Thanks but I tried slowing down the code. Didn't help with the memory issue.

I pasted a few parts of code from different VBA's I'd written before that had the same function, in one I used Sheets, and in one Worksheets so this is a collection of both...

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 6450
  • Total likes: 2744
  • DansDeals.com Hat Tips 269
    • View Profile
Re: Excel Help and Problems
« Reply #1351 on: February 07, 2020, 10:06:32 AM »
Is there any way to make a "calendar view" by giving it specific dates?

Meaning, I have events on 2/15, 2/17, 3/1, 3/2, 3/9, 4/6...etc... is there a way to have excel (or anything) create a monthly calendar format with my data showing on the appropriate dates? So in this case, I will still see the whole month of Feb but the 15th and 17th will show my events.
If it's not free shipping it's not worth it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3809
  • Total likes: 822
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1352 on: February 07, 2020, 10:19:20 AM »
Is there any way to make a "calendar view" by giving it specific dates?

Meaning, I have events on 2/15, 2/17, 3/1, 3/2, 3/9, 4/6...etc... is there a way to have excel (or anything) create a monthly calendar format with my data showing on the appropriate dates? So in this case, I will still see the whole month of Feb but the 15th and 17th will show my events.
Easiest thing to do is probably to import the list from Excel into Outlook (something like this - https://www.techwalla.com/articles/create-calendar-excel-data), but I assume you're looking to continually change the list and have it update the calendar, which won't work with this method. I'm sure there are ways to do it with pivot tables as well, but that's probably more hassle than it's worth. I would suggest looking through the calendar templates and see if any work for what you're trying to do. There's one called Student assignment planner that should work for you (ie replace assignment with event).

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 6450
  • Total likes: 2744
  • DansDeals.com Hat Tips 269
    • View Profile
Re: Excel Help and Problems
« Reply #1353 on: February 07, 2020, 10:28:39 AM »
I would suggest looking through the calendar templates and see if any work for what you're trying to do. There's one called Student assignment planner that should work for you (ie replace assignment with event).

Thanks! How do you replace the "Assignment due!" with the event?
If it's not free shipping it's not worth it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3809
  • Total likes: 822
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1354 on: February 07, 2020, 10:33:51 AM »
Thanks! How do you replace the "Assignment due!" with the event?
The first tab of the template is Assignments. You can change the tab name, and the header (row 2), to Event(s) or whatever you want. Then in each row (from row 4 and on), put the event title/description in column B and the date of the event in column C (clear any rows you don't need). Then when you go to the Month or Week View tabs you'll see the events you put in populated. You can switch the month or year you want to view in row 3 of the those tabs.

ETA: In case it wasn't clear, you don't enter the events on the month tab - it's filled with formulas to read off of the Assignments tab, which is where you enter them.

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 6450
  • Total likes: 2744
  • DansDeals.com Hat Tips 269
    • View Profile
Re: Excel Help and Problems
« Reply #1355 on: February 07, 2020, 10:40:44 AM »
The first tab of the template is Assignments. You can change the tab name, and the header (row 2), to Event(s) or whatever you want. Then in each row (from row 4 and on), put the event title/description in column B and the date of the event in column C (clear any rows you don't need). Then when you go to the Month or Week View tabs you'll see the events you put in populated. You can switch the month or year you want to view in row 3 of the those tabs.

ETA: In case it wasn't clear, you don't enter the events on the month tab - it's filled with formulas to read off of the Assignments tab, which is where you enter them.

Yes I did all that but the formula is this:  =IF(LEN(G8)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,@MoMonthNum,G8))>0,"Assignment due!",""))  so when it's true it's just showing Assignment due! , how do I get it to show the value of the corresponding date from the fist tab?
If it's not free shipping it's not worth it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3809
  • Total likes: 822
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1356 on: February 07, 2020, 11:00:59 AM »
Yes I did all that but the formula is this:  =IF(LEN(G8)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,@MoMonthNum,G8))>0,"Assignment due!",""))  so when it's true it's just showing Assignment due! , how do I get it to show the value of the corresponding date from the fist tab?
Lol, sorry - hadn't tested. In B7, replace the formula with:
=IF(LEN(B6)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,MoMonthNum,B6))>0,INDEX(Assignments[DESCRIPTION],MATCH(DATE(MoYear,MoMonthNum,B6),Assignments[DUE DATE],0)),""))

Then copy B7 and paste formula to B7:H7, and again for rows 9, 11, 13, 15, and 17.

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 6450
  • Total likes: 2744
  • DansDeals.com Hat Tips 269
    • View Profile
Re: Excel Help and Problems
« Reply #1357 on: February 07, 2020, 11:06:13 AM »
Lol, sorry - hadn't tested. In B7, replace the formula with:
=IF(LEN(B6)=0,"",IF(COUNTIF(Assignments[DUE DATE],DATE(MoYear,MoMonthNum,B6))>0,INDEX(Assignments[DESCRIPTION],MATCH(DATE(MoYear,MoMonthNum,B6),Assignments[DUE DATE],0)),""))

Then copy B7 and paste formula to B7:H7, and again for rows 9, 11, 13, 15, and 17.

Sick!! That's awesome, thanks so so much!!
If it's not free shipping it's not worth it.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17361
  • Total likes: 14274
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1358 on: March 03, 2020, 06:05:28 PM »
How can I get half-hourly data which looks like this
 
2/1/2019 0:30
402.48
2/1/2019 1:00
399.6
2/1/2019 1:30
392.4
2/1/2019 2:00
386.64
2/1/2019 2:30
374.4
2/1/2019 3:00
373.68
2/1/2019 3:30
365.04
2/1/2019 4:00
369.36
2/1/2019 4:30
381.96
converted into hourly data formatted like this
11/1/2018
1
166.32
11/1/2018
2
147.96
11/1/2018
3
144.36
11/1/2018
4
146.16
11/1/2018
5
153.72
11/1/2018
6
170.64
11/1/2018
7
176.04
11/1/2018
8
179.28
11/1/2018
9
178.56
11/1/2018
10
164.16
11/1/2018
11
130.68
11/1/2018
12
138.24
11/1/2018
13
135
or this
 
11/1/18 1:00
166.32
11/1/18 2:00
147.96
11/1/18 3:00
144.36
11/1/18 4:00
146.16
11/1/18 5:00
153.72
11/1/18 6:00
170.64
11/1/18 7:00
176.04
11/1/18 8:00
179.28
11/1/18 9:00
178.56
11/1/18 10:00
164.16
11/1/18 11:00
130.68
11/1/18 12:00
138.24
11/1/18 13:00
135
Feelings don't care about your facts

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4375
  • Total likes: 1315
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1359 on: March 03, 2020, 06:20:44 PM »
How can I get half-hourly data which looks like this
 
2/1/2019 0:30
402.48
2/1/2019 1:00
399.6
2/1/2019 1:30
392.4
2/1/2019 2:00
386.64
2/1/2019 2:30
374.4
2/1/2019 3:00
373.68
2/1/2019 3:30
365.04
2/1/2019 4:00
369.36
2/1/2019 4:30
381.96
converted into hourly data formatted like this
11/1/2018
1
166.32
11/1/2018
2
147.96
11/1/2018
3
144.36
11/1/2018
4
146.16
11/1/2018
5
153.72
11/1/2018
6
170.64
11/1/2018
7
176.04
11/1/2018
8
179.28
11/1/2018
9
178.56
11/1/2018
10
164.16
11/1/2018
11
130.68
11/1/2018
12
138.24
11/1/2018
13
135
or this
 
11/1/18 1:00
166.32
11/1/18 2:00
147.96
11/1/18 3:00
144.36
11/1/18 4:00
146.16
11/1/18 5:00
153.72
11/1/18 6:00
170.64
11/1/18 7:00
176.04
11/1/18 8:00
179.28
11/1/18 9:00
178.56
11/1/18 10:00
164.16
11/1/18 11:00
130.68
11/1/18 12:00
138.24
11/1/18 13:00
135
What are those numbers following the time? And are the in the same cell?