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

Offline cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2321
  • Total likes: 400
  • DansDeals.com Hat Tips 4
    • 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: 5793
  • Total likes: 1440
  • DansDeals.com Hat Tips 260
    • 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: 3144
  • Total likes: 305
  • 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: 5793
  • Total likes: 1440
  • DansDeals.com Hat Tips 260
    • 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: 3144
  • Total likes: 305
  • 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: 5793
  • Total likes: 1440
  • DansDeals.com Hat Tips 260
    • 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: 3144
  • Total likes: 305
  • 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: 5793
  • Total likes: 1440
  • DansDeals.com Hat Tips 260
    • 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: 11281
  • Total likes: 5227
  • DansDeals.com Hat Tips 8
    • 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 Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2321
  • Total likes: 400
  • DansDeals.com Hat Tips 4
    • 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?

Offline cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2321
  • Total likes: 400
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1360 on: March 03, 2020, 06:25:46 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
Try:
=MROUND(A1,1/24)
Change A1 to the cell containing your data, and double click the fill handle to repeat the formula for all your instances.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 11281
  • Total likes: 5227
  • DansDeals.com Hat Tips 8
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1361 on: March 03, 2020, 08:36:10 PM »
What are those numbers following the time? And are the in the same cell?
One cell is date and time the other is the value. I don't care if in the end result the date and time are separated as long as the end result has the total value of each hour added together.
Feelings don't care about your facts

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3144
  • Total likes: 305
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1362 on: March 04, 2020, 12:50:56 AM »
One cell is date and time the other is the value. I don't care if in the end result the date and time are separated as long as the end result has the total value of each hour added together.
https://www.excelcampus.com/charts/group-times-in-excel/

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 11281
  • Total likes: 5227
  • DansDeals.com Hat Tips 8
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1363 on: March 04, 2020, 07:33:18 AM »
https://www.excelcampus.com/charts/group-times-in-excel/
These examples are counting transactions. The more similar example with their data would be to get the sum of the dollar amount per hour. I was able to get hours summed together using PivotTable but it is grouping the hours differently from the way the labeling intends. It also doesn't give the date and time back in the way I need it. I am going to try playing around with the concepts here though. Thanks!
Feelings don't care about your facts

Offline the great

  • Dansdeals Bronze Elite
  • *
  • Join Date: Jun 2010
  • Posts: 43
  • Total likes: 3
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1364 on: March 04, 2020, 08:35:46 AM »
Anyone know if itís possible to make part of a formula to come out underlined?