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

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2645
  • Total likes: 48
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1335 on: December 17, 2019, 08:52:58 PM »
Get a list with each name once
Next to each name the formula is =COUNTIF(range with the names in the worksheet, cell with the name)
Make sure the range is locked.

For example, if the names you want to count are in C1:C100, and you have a list of names in column E, then F1 would be = COUNTIF($C$1:$C$100,E1)

Offline jose34

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2016
  • Posts: 1682
  • Total likes: 70
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1336 on: December 17, 2019, 09:10:37 PM »
not working, what about using the pivot table?

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2645
  • Total likes: 48
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1337 on: December 17, 2019, 09:11:23 PM »
not working, what about using the pivot table?
That would work also

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1465
  • Total likes: 35
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: USA
Re: Excel Help and Problems
« Reply #1338 on: December 18, 2019, 10:25:03 AM »
Get a list with each name once
Next to each name the formula is =COUNTIF(range with the names in the worksheet, cell with the name)
Make sure the range is locked.

For example, if the names you want to count are in C1:C100, and you have a list of names in column E, then F1 would be = COUNTIF($C$1:$C$100,E1)
To get a list of each name once in a large sheet, the fastest way is to use Google sheets' UNIQUE function.

Actually, office 365 had it as well?
https://exceljet.net/excel-functions/excel-unique-function

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11398
  • Total likes: 1420
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1339 on: December 18, 2019, 10:33:42 AM »
To get a list of each name once in a large sheet, the fastest way is to use Google sheets' UNIQUE function.

Actually, office 365 had it as well?
https://exceljet.net/excel-functions/excel-unique-function
I usually select the column and click Data > Remove Duplicates
Workflowy. You won't know what you're missing until you try it.

Offline Zalc

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1465
  • Total likes: 35
  • 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: 11398
  • Total likes: 1420
  • 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: 1465
  • Total likes: 35
  • 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: 11398
  • Total likes: 1420
  • 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: 855
  • Total likes: 22
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • 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?

Online cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2210
  • Total likes: 380
  • DansDeals.com Hat Tips 4
    • 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: 5151
  • Total likes: 167
  • 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?

Online cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2210
  • Total likes: 380
  • DansDeals.com Hat Tips 4
    • 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: 3089
  • Total likes: 236
  • 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: 855
  • Total likes: 22
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • 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 »