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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3794
  • Total likes: 820
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1520 on: March 30, 2022, 02:40:54 PM »
If anyone can help me with a simple conditional formatting I'd really appreciate it. I'm trying to compare two lists and highlight cells in column 1 that also appear in the list on sheet 2. Tried google and for some reason it is not working.
Let's say list 1 is in column A of Sheet1 and list list 2 is in column A of Sheet2. Highlight list 1 > Conditional Formatting > New Rule > click the last option (Use a formula...), then enter the following into the Formula box:
=COUNTIF(Sheet2!$A:$A,A1)>=1
Then you can click format to choose your desired formatting.

Keep in mind that this version of the formula will be slow if you apply it to massive lists. There are ways to make it more efficient if necessary.

Offline hocker

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2009
  • Posts: 3499
  • Total likes: 84
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1521 on: March 30, 2022, 05:49:18 PM »
Let's say list 1 is in column A of Sheet1 and list list 2 is in column A of Sheet2. Highlight list 1 > Conditional Formatting > New Rule > click the last option (Use a formula...), then enter the following into the Formula box:
=COUNTIF(Sheet2!$A:$A,A1)>=1
Then you can click format to choose your desired formatting.

Keep in mind that this version of the formula will be slow if you apply it to massive lists. There are ways to make it more efficient if necessary.
TY

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 6521
  • Total likes: 2480
  • DansDeals.com Hat Tips 59
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1522 on: March 30, 2022, 10:29:08 PM »
I believe vlookup is what you want
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17256
  • Total likes: 14050
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1523 on: March 30, 2022, 11:13:21 PM »
Is there a simple way to sum a cell from each tab in the sheet without referencing them individually?
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12811
  • Total likes: 3253
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1524 on: March 30, 2022, 11:15:27 PM »
Is there a simple way to sum a cell from each tab in the sheet without referencing them individually?
Code: [Select]
=SUM(Sheet1:Sheet3!A1)
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17256
  • Total likes: 14050
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1525 on: March 30, 2022, 11:18:58 PM »
Feelings don't care about your facts

Offline yuneeq

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2013
  • Posts: 8587
  • Total likes: 3981
  • DansDeals.com Hat Tips 10
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1526 on: August 04, 2022, 10:10:26 AM »
More accurately,
2-Jan

Why the most useless date format is the default is another question

For a program that was created to work with numbers, Excel is absolutely horrendous at formatting them. 164849398282 by default turns into scientific notation like 163748e+3, you convert it to a number and half the time it gets truncated and turned into zeroes at the end. So you get lucky one time and it converts over cleanly, you save it, and open the file again and itís back in the old useless format. Or when your number starts with a zero. You try putting a zero before, convert to text and pray it stays.

Or the times thereís a number in a cell and it doesnít recognize it as a number even if you try converting it to a number, currency, or anything else. So you canít sum it or use it in any formula. But once you double click on the cell, the number magically awakens, aligns itself to the right, and is suddenly recognized as a valid number.

Iím sure there are ways to solve the issues mentioned above, but typically the solution is tedious, unintuitive hacks.

Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.
Visibly Jewish

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5060
  • Total likes: 20
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Help and Problems
« Reply #1527 on: August 04, 2022, 10:13:10 AM »
The TEXT formula is very useful.

* 1 can force the text to be treated like a number. You can use paste special > multiply for that if you dont want to have to add in a column
« Last Edit: August 04, 2022, 10:16:27 AM by bubbles »

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5715
  • Total likes: 1374
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: Excel Help and Problems
« Reply #1528 on: August 04, 2022, 10:18:06 AM »
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3794
  • Total likes: 820
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1529 on: August 04, 2022, 02:47:19 PM »
Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.
Regarding cell formatting resetting, what file format are you using?

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17256
  • Total likes: 14050
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1530 on: August 05, 2022, 12:22:49 PM »
Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.
For long strings of digits to display as text add a ' before it or format the cell/column/row/sheet as text before entering the string. If you are then pasting paste only values.

Another option is to use text to columns and select delineated and hit finish right away without adding a delineator. I think the delineator needs to be added, but it is possible that I simply have it set on a delineator that is not found in the sequences. If that becomes a problem then just go to the next step and ensure there is either none at all or one that will not be found in the data set. I have this programmed into some macros for sheets that are exported from online data and have multiple unformatted columns. I think that the concept was given to me in a response on this thread.
Feelings don't care about your facts

Offline yuneeq

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2013
  • Posts: 8587
  • Total likes: 3981
  • DansDeals.com Hat Tips 10
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1531 on: August 05, 2022, 04:56:54 PM »
Regarding cell formatting resetting, what file format are you using?

CSVs or XLSX
Many times itís downloaded reports that come formatted improperly.

For long strings of digits to display as text add a ' before it or format the cell/column/row/sheet as text before entering the string. If you are then pasting paste only values.

Another option is to use text to columns and select delineated and hit finish right away without adding a delineator. I think the delineator needs to be added, but it is possible that I simply have it set on a delineator that is not found in the sequences. If that becomes a problem then just go to the next step and ensure there is either none at all or one that will not be found in the data set. I have this programmed into some macros for sheets that are exported from online data and have multiple unformatted columns. I think that the concept was given to me in a response on this thread.

Thanks!
Visibly Jewish

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3794
  • Total likes: 820
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1532 on: August 05, 2022, 06:12:17 PM »
CSVs or XLSX
Many times itís downloaded reports that come formatted improperly.

Thanks!
CSV won't save your formatting. If you have a CSV and make formatting changes, save it as XLSX or similar.

Offline yuneeq

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2013
  • Posts: 8587
  • Total likes: 3981
  • DansDeals.com Hat Tips 10
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1533 on: August 05, 2022, 06:14:57 PM »
CSV won't save your formatting. If you have a CSV and make formatting changes, save it as XLSX or similar.

Yeh I never save as CSV. Only use it when itís downloaded like that and change it right away.
Visibly Jewish

Online Euclid

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4856
  • Total likes: 5994
  • DansDeals.com Hat Tips 4
    • View Profile

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 764
  • Total likes: 9
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1535 on: August 16, 2022, 10:32:15 PM »
Anyone here write/code in VBA? I have a job opening, monsey area if you have experience( prefer SNF experience)

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17256
  • Total likes: 14050
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1536 on: October 19, 2022, 05:19:35 PM »
I have an excel tab that has monthly numbers for a number of different metrics with ROW1 listing the metric that the data is for. COL A is the year and COL B is the month.  On a different tab I have set up to do an HLOOKUP by the metric from a pulldown menu with the rows representing months and the columns representing years (each year is it's own column) I currently have each cell do an HLOOKUP using a formula like this =HLOOKUP(metric,sheet,115,FALSE) and each cell has the row corresponding to that month and year. The issue I am running into is that when there is no value it returns $0.00 which skews the conditional formatting of a color scale of highest to lowest values. I have been replacing the HLOOKUP with a formula like this one which places a -- in the cell instead of a zero value =IF((HLOOKUP(metric,sheet,38,FALSE))=0,"--",HLOOKUP(metric,sheet,38,FALSE)). The issue is that when I copy/paste it keeps the row number from the source even when dragging down and I have needed to change it manually.
1) Is there an overall better way to do this?2) Is there a way to paste (or replace) with it keeping the row number of the hlookup from the formula currently in the cell or to have it adjust the row when dragging or copy/pasting?
Feelings don't care about your facts

Offline Definitions

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jan 2018
  • Posts: 1748
  • Total likes: 479
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Help and Problems
« Reply #1537 on: October 19, 2022, 10:46:31 PM »
I have an excel tab that has monthly numbers for a number of different metrics with ROW1 listing the metric that the data is for. COL A is the year and COL B is the month.  On a different tab I have set up to do an HLOOKUP by the metric from a pulldown menu with the rows representing months and the columns representing years (each year is it's own column) I currently have each cell do an HLOOKUP using a formula like this =HLOOKUP(metric,sheet,115,FALSE) and each cell has the row corresponding to that month and year. The issue I am running into is that when there is no value it returns $0.00 which skews the conditional formatting of a color scale of highest to lowest values. I have been replacing the HLOOKUP with a formula like this one which places a -- in the cell instead of a zero value =IF((HLOOKUP(metric,sheet,38,FALSE))=0,"--",HLOOKUP(metric,sheet,38,FALSE)). The issue is that when I copy/paste it keeps the row number from the source even when dragging down and I have needed to change it manually.
1) Is there an overall better way to do this?2) Is there a way to paste (or replace) with it keeping the row number of the hlookup from the formula currently in the cell or to have it adjust the row when dragging or copy/pasting?

Not sure if im picturing the sheet correctly. Is screenshot correct? Why is this a problem only when you have the IF formula?

Either way would adding an id column which is a concat of the year and month be an option? if yes then for the row number you can do a MATCH on the ID column using the year and month concatenated as a value. (you can keep the row number absolutely referenced.

Likely not an elegant solution but its the easiest I can think of
My Tapatalk notifications don't always work.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17256
  • Total likes: 14050
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1538 on: October 19, 2022, 11:17:08 PM »
Not sure if im picturing the sheet correctly. Is screenshot correct? Why is this a problem only when you have the IF formula?

Either way would adding an id column which is a concat of the year and month be an option? if yes then for the row number you can do a MATCH on the ID column using the year and month concatenated as a value. (you can keep the row number absolutely referenced.

Likely not an elegant solution but its the easiest I can think of

It isn't only with the if formula but that is what I am trying to get into a couple hundred cells right now and each one has the row number twice.

To use your solution would require index matching for both the top col and the left column. Also, I would need to have a formula in the lookup (twice) to concatenate the month from the left and the year from the top to have the data to match to. This seems like it would slow down the sheet immensely if there are many cells with such a formula.
Feelings don't care about your facts

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3794
  • Total likes: 820
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1539 on: October 20, 2022, 12:17:40 AM »
It isn't only with the if formula but that is what I am trying to get into a couple hundred cells right now and each one has the row number twice.

To use your solution would require index matching for both the top col and the left column. Also, I would need to have a formula in the lookup (twice) to concatenate the month from the left and the year from the top to have the data to match to. This seems like it would slow down the sheet immensely if there are many cells with such a formula.
What is the hardcoded number you're using for row? Why can't that be replaced with a formula?

Also, switching from HLOOKUP to INDEX, even with MATCH for both column and row, would not slow it down. If anything it would speed it up. Concatenate is a non-issue (you could also just do "month&year").