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

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 13299
  • Total likes: 3973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1440 on: January 10, 2021, 05:08:46 PM »
I have a google sheet with a column with links to an mp3, a column for artist and a column for title, is there any way to have it automatically download all the mp3s with the filename based on the artist and title column?
This extension might help https://www.downthemall.net/

I don't know how it determines the file names of the downloaded files - but if it uses the display text, you could use formulas to prep an html file with all the links, save locally on your computer, and then use the extension to download them
Workflowy. You won't know what you're missing until you try it.

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 7468
  • Total likes: 3091
  • DansDeals.com Hat Tips 66
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1441 on: January 10, 2021, 07:13:51 PM »
This extension might help https://www.downthemall.net/

I don't know how it determines the file names of the downloaded files - but if it uses the display text, you could use formulas to prep an html file with all the links, save locally on your computer, and then use the extension to download them
Thanks that's exactly the question, I doubt it has the ability to custom name it. I assume it would just use the file name as uploaded
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 13299
  • Total likes: 3973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1442 on: January 10, 2021, 07:19:15 PM »
Thanks that's exactly the question, I doubt it has the ability to custom name it. I assume it would just use the file name as uploaded
I figured as well. If you do end up downloading them with the existing filenames, you can use the program mp3tag to rename them and set the metadata. It's a great program.
Workflowy. You won't know what you're missing until you try it.

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 7468
  • Total likes: 3091
  • DansDeals.com Hat Tips 66
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1443 on: January 10, 2021, 07:26:59 PM »
I figured as well. If you do end up downloading them with the existing filenames, you can use the program mp3tag to rename them and set the metadata. It's a great program.
Thanks. The problem is the original file names are very random, so I won't know what's what..
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 13299
  • Total likes: 3973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1444 on: January 10, 2021, 07:28:46 PM »
Thanks. The problem is the original file names are very random, so I won't know what's what..
Based on your google sheet. How long is the list?
Workflowy. You won't know what you're missing until you try it.

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 7468
  • Total likes: 3091
  • DansDeals.com Hat Tips 66
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1445 on: January 10, 2021, 07:30:19 PM »
Based on your google sheet. How long is the list?
My precise use case is for hundreds of files, the above was an example to make it clear. (wouldn't be asking otherwise ;))
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 13299
  • Total likes: 3973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1446 on: January 10, 2021, 07:31:08 PM »
My precise use case is for hundreds of files, the above was an example to make it clear.
Hmm, it might be worth looking into writing a google apps script for that. IDK for sure that it could do this but I would hope it can.
Workflowy. You won't know what you're missing until you try it.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5391
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile

Offline AsherO

  • Global Moderator
  • Dansdeals Lifetime 25K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 32047
  • Total likes: 8912
  • DansDeals.com Hat Tips 79
    • View Profile
  • Location: NYC
Re: Excel Help and Problems
« Reply #1448 on: January 25, 2021, 10:49:38 PM »
I have a google sheet with a column with links to an mp3, a column for artist and a column for title, is there any way to have it automatically download all the mp3s with the filename based on the artist and title column?

If it’s a one time thing, download to Excel and find a macro that does this,  I’m confident Google will locate one.
DDF FFB (Forum From Birth)

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 7468
  • Total likes: 3091
  • DansDeals.com Hat Tips 66
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1449 on: January 25, 2021, 10:52:04 PM »
If it’s a one time thing, download to Excel and find a macro that does this,  I’m confident Google will locate one.
Thanks.

I ended up paying a professional since it was time sensitive. If anyone would like the code PM me.
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline Definitions

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jan 2018
  • Posts: 1792
  • Total likes: 481
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Help and Problems
« Reply #1450 on: March 09, 2021, 07:19:55 PM »
Any reliable way that I can insert a screenshot into a cell then either manually make it smaller or have it pop out only when clicked on.

It has to be obvious which row it's on.

Will just pasting it into the cell mess up the positioning once I email the file?
My Tapatalk notifications don't always work.

Offline Definitions

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jan 2018
  • Posts: 1792
  • Total likes: 481
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Help and Problems
« Reply #1451 on: March 09, 2021, 07:37:40 PM »
Nevermind. It seems to be working fine
My Tapatalk notifications don't always work.

Offline justaregularguy

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2014
  • Posts: 1769
  • Total likes: 1494
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Not Brooklyn
Re: Excel Help and Problems
« Reply #1452 on: March 09, 2021, 11:19:02 PM »
I dont really have a question as much as an insight.

Vlookup is NOT case sensitive. I know in general this isn’t a problem for most ppl using vlookup. But I had to match hundreds of ID’s and I thought i got it right w the vlookup. Then I noticed an ID  like aeB300000nK was being matched with a file that’s ID was really aeB300000nk (Notice the lower case k)
So it messed up the vlookup for those .
So there are some ways to vlookup case sensitive I’m curious to know ddfers methods on doing this. One formula I found for some reason was not working using exact() and row()

Anyway, I found a cool small macro that’s does the job quite well and works just like vlookup :)

If anyone’s interested in the macro let me know :)
nothings impossible- the word itself says Im possible

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 13299
  • Total likes: 3973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1453 on: March 09, 2021, 11:20:25 PM »
I don  really have a question as much as an insight.

Vlookup is NOT case sensitive. I know in general this isn’t a problem for most ppl using vlookup. But I had to match hundreds of ID’s and I thought u got it right w the vlookup. Then I noticed an ID  like aeB300000nK was being matched with a file that’s ID was really aeB300000nk (Notice the lower case k)
So it messed up the vlookup for those .
So there are some ways to vlookup case sensitive I’m curious to know ddfers methods on doing this. One formula I found for some reason was not working using exact() and row()

Anyway, I found a cool small macro that’s does the job quite well and works just like vlookup :)
You can use a combination of INDEX and MATCH for this. I don't know it offhand, I just google it when relevant and copy/paste.
Workflowy. You won't know what you're missing until you try it.

Offline justaregularguy

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2014
  • Posts: 1769
  • Total likes: 1494
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Not Brooklyn
Re: Excel Help and Problems
« Reply #1454 on: March 09, 2021, 11:22:07 PM »
You can use a combination of INDEX and MATCH for this. I don't know it offhand, I just google it when relevant and copy/paste.
it’s funny I’m familiar w some formulas and totally not others
I never use index match
What wud the formula be?
nothings impossible- the word itself says Im possible

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 19716
  • Total likes: 16295
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1455 on: March 10, 2021, 12:27:00 AM »
it’s funny I’m familiar w some formulas and totally not others
I never use index match
What wud the formula be?
Index match is much more powerful
Feelings don't care about your facts

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5084
  • Total likes: 20
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Help and Problems
« Reply #1456 on: March 10, 2021, 12:40:34 AM »
Check out the new xlookup. Takes care of all the issues with vlookup and much more straightforward than index match

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 13299
  • Total likes: 3973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1457 on: March 10, 2021, 07:48:04 AM »
Check out the new xlookup. Takes care of all the issues with vlookup and much more straightforward than index match
Right I forgot about that one. Still need to try it out (IIRC it comes with office 2019)
Workflowy. You won't know what you're missing until you try it.

Offline justaregularguy

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2014
  • Posts: 1769
  • Total likes: 1494
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Not Brooklyn
Re: Excel Help and Problems
« Reply #1458 on: March 10, 2021, 07:59:36 PM »
Check out the new xlookup. Takes care of all the issues with vlookup and much more straightforward than index match
what the heck is that ? Does it come w office 365?

As an aside does anyone know what LAMBDA is?
nothings impossible- the word itself says Im possible

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 7468
  • Total likes: 3091
  • DansDeals.com Hat Tips 66
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1459 on: March 10, 2021, 08:02:44 PM »
what the heck is that ? Does it come w office 365?

As an aside does anyone know what LAMBDA is?
GIYF, my friend.
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/