Excel Help and Problems

[Zalc]

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.

[etech0]

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

[Zalc]

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

[etech0]

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!!

[Der Deutsche Jude]

Any interest in having a VBA thread?

[cgr]

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.

[EJB]

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?

[cgr]

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.

[skyguy918]

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.

[Der Deutsche Jude]

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

[cgr]

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...

[stooges44]

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.

[skyguy918]

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).

[stooges44]

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?

[skyguy918]

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.

[stooges44]

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?

[skyguy918]

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.

[stooges44]

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!!

[aygart]

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

[cgr]

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?