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

Offline Der Deutsche Jude

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Jan 2016
  • Posts: 841
  • Total likes: 21
  • 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 #1380 on: March 11, 2020, 06:25:30 AM »
Maybe try saving the template as read-only?
Thanks but since I donít want to password protect it, it will only display a message suggesting that the user open it as read only.

Offline villamsin

  • DansDeals Copper Elite
  • *
  • Join Date: Mar 2020
  • Posts: 3
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: London
Re: Excel Help and Problems
« Reply #1381 on: March 11, 2020, 10:11:51 PM »
I have a worksheet with open password but I can not figure out what is. It is an old file from Office 2010. Possible to recover the password?

Offline Definitions

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Jan 2018
  • Posts: 984
  • Total likes: 138
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel help
« Reply #1382 on: April 13, 2020, 02:50:26 AM »
I'm trying to make address labels but when I merge in word it keeps dropping the "0" in zip codes starting with "0". I can't find a fix online- does anyone know what I need to do?
I know this is really old and you found a solution.

I had the same issue recently this fixed it for me
Jack of all trades, master of none.

Offline Definitions

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Jan 2018
  • Posts: 984
  • Total likes: 138
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1383 on: April 14, 2020, 03:26:05 PM »
How do I get it to autofill a pattern 1,2,1,2...?

This is what happens to me
Jack of all trades, master of none.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 10635
  • Total likes: 781
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1384 on: April 14, 2020, 03:31:56 PM »
After you do the dragging, there might be a little icon floating under the cells, if you click it you can switch it to 'pattern' I think.
Workflowy. You won't know what you're missing until you try it.

Offline SrulyS

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2015
  • Posts: 597
  • Total likes: 55
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1385 on: April 14, 2020, 03:46:01 PM »
How do I get it to autofill a pattern 1,2,1,2...?

This is what happens to me

Try holding down the ctrl key while filling.

Offline Definitions

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Jan 2018
  • Posts: 984
  • Total likes: 138
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1386 on: April 14, 2020, 03:49:00 PM »
After you do the dragging, there might be a little icon floating under the cells, if you click it you can switch it to 'pattern' I think.
There's no option if pattern. There is an option to copy cells. Does the job. Thanks
Try holding down the ctrl key while filling.

That also works. Thanks
Jack of all trades, master of none.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 10635
  • Total likes: 781
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1387 on: April 14, 2020, 03:58:45 PM »
Try holding down the ctrl key while filling.
Nice!
Workflowy. You won't know what you're missing until you try it.

Offline cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 1139
  • Total likes: 188
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1388 on: April 23, 2020, 03:42:59 PM »
I wrote a macro that imports a whole bunch of csv files based on a list. The macro then cuts down the imported files that have thousands of rows to about 60 rows based on certain criteria.
Recently, the csv files I need to import have over 1MM rows. Excel can only load 1,048,576 rows of data.
I'm seeing that it's possible to open csv files in Excel Power Query, which has can hold a few million rows.
Anyone know how to load csv files into Power Query based on a list? How to automate this via vba so I don't have to manually point to the list? And how to then run my remaining code to thin out the data on Power Query?
Any help on this would be appreciated.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2641
  • Total likes: 46
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1389 on: April 23, 2020, 04:52:49 PM »
I wrote a macro that imports a whole bunch of csv files based on a list. The macro then cuts down the imported files that have thousands of rows to about 60 rows based on certain criteria.
Recently, the csv files I need to import have over 1MM rows. Excel can only load 1,048,576 rows of data.
I'm seeing that it's possible to open csv files in Excel Power Query, which has can hold a few million rows.
Anyone know how to load csv files into Power Query based on a list? How to automate this via vba so I don't have to manually point to the list? And how to then run my remaining code to thin out the data on Power Query?
Any help on this would be appreciated.

You can't run VBA macros in Power Query but you should be able filter the CSV in PQ based on whatever criteria you have.
Theoretically, in Excel if you would do a vlookup on each row to see if it's in the list, and then remove anything that's not found, would that satisfy your requirement? if yes then it can be done in PQ with a merge and then filter the blanks (or do an inner join but that's much slower) .

Regarding the multiple files - you can create a function to clean up the data and then loop thru all the files and have the function run on each file, but there's no UI for that, and it's somewhat advanced. This should help get you started with looping.
Also you can point PQ to a folder with all the files then when you have new files you can just replace the files in the folder.

Only caveat is that as far as I know PQ can't write to other excel files (In other words PQ won't manipulate the outside source file, rather it imports the data, then you can transform the data as needed, and then spit it out into the current excel file). You might be able to output them in separate tabs and then you can use VBA to separate if needed.

Let me know if I can be of further help.
« Last Edit: April 23, 2020, 05:01:02 PM by yitzf »

Offline cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 1139
  • Total likes: 188
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1390 on: April 23, 2020, 06:27:23 PM »
You can't run VBA macros in Power Query but you should be able filter the CSV in PQ based on whatever criteria you have.
Theoretically, in Excel if you would do a vlookup on each row to see if it's in the list, and then remove anything that's not found, would that satisfy your requirement? if yes then it can be done in PQ with a merge and then filter the blanks (or do an inner join but that's much slower) .

Regarding the multiple files - you can create a function to clean up the data and then loop thru all the files and have the function run on each file, but there's no UI for that, and it's somewhat advanced. This should help get you started with looping.
Also you can point PQ to a folder with all the files then when you have new files you can just replace the files in the folder.

Only caveat is that as far as I know PQ can't write to other excel files (In other words PQ won't manipulate the outside source file, rather it imports the data, then you can transform the data as needed, and then spit it out into the current excel file). You might be able to output them in separate tabs and then you can use VBA to separate if needed.

Let me know if I can be of further help.

Thanks for the response!
I'm actually looking to save the queried data to the open file, so that part shouldn't be an issue. Just not so sure I can work around some other bumps (all source files have to be located in the same folder but not all are used for each data file I'm trying to create, also each connection will have a different name once queried, and will vary based on the imports so not sure I'll be able to automatically run a function to merge out the values I don't want). Will need to do some more research...

Offline shnu

  • Dansdeals Silver Elite
  • **
  • Join Date: Jul 2011
  • Posts: 88
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1391 on: April 26, 2020, 06:13:24 PM »
How would I email from excel, without having to use outlook? The only way now I can is by saving to file and then going to gmail and selecting.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2641
  • Total likes: 46
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1392 on: April 26, 2020, 07:38:05 PM »
How would I email from excel, without having to use outlook? The only way now I can is by saving to file and then going to gmail and selecting.
https://www.rondebruin.nl/win/addins/rdbmail.htm
You might need outlook installed on the computer.

Offline shnu

  • Dansdeals Silver Elite
  • **
  • Join Date: Jul 2011
  • Posts: 88
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1393 on: April 27, 2020, 09:41:49 PM »
Thanks!

Offline cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 1139
  • Total likes: 188
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1394 on: April 28, 2020, 04:49:49 PM »
You can't run VBA macros in Power Query
I was able pass through all my mquery code through VBA so I can now run everything automatically, as well as run my code across multiple files, only import specific files instead of entire folders contents, etc etc.