***Official*** Excel Help Corner (5 Viewers)

[BroadwayG]

I just copy/pasted this fine as text.  No idea why you're not able to do it.

You said you've created a file and then imported it... is the file layout the same every time?  If so, you can easily macro the import (I understand how annoying the import wizard is).

Gah, it's just my curse then. I cannot get anything but this when I paste:


1234


1.86E+15


2-Mar



Google Sheets: pastes fine, no formatting.  Excel Online: Same crapola as desktop program.

 

[Mr. Ected]

I just copy/pasted this fine as text.  No idea why you're not able to do it.

You said you've created a file and then imported it... is the file layout the same every time?  If so, you can easily macro the import (I understand how annoying the import wizard is).

Gah, it's just my curse then. I cannot get anything but this when I paste:


1234


1.86E+15


2-Mar

Google Sheets: pastes fine, no formatting.  Excel Online: Same crapola as desktop program.

I have the same problem. Just so you aren't alone!!!

 

[Ned]

Any way you guys can post a sample sheet?  This is how it looks when I copied it from Chrome and then did Paste Special - Match Destination Formatting

https://s30.postimg.org/e5jhkjl1t/Broadway_G_Excel.png

 

[Mr. Ected]

OK I googled it and got an answer for the Zip. One simple answer is to put the ' in front of the number, but that treats it as text, and Excel gets it's panties in a wad about that and puts the green Sehorn in the corner because you are 'treating a number as text.'

Here is what I found and it works.

You can correct this formatting within Excel using two different methods.

1a. Select the entire Zip Code column from within Excel.
1b. Select the menu option Format -> Cells...
1c. Select the Number tab, and click on the category called "Special".
1d. You can then select the type Zip Code or Zip Code + 4, and then click OK.

 

[BroadwayG]

Any way you guys can post a sample sheet?  This is how it looks when I copied it from Chrome and then did Paste Special - Match Destination Formatting

https://s30.postimg.org/e5jhkjl1t/Broadway_G_Excel.png

Match Destination Formatting must be a newer option, I'm a couple versions behind.  I'm glad they finally figured out a solution for this after 20+ years.

 

[Mr. Ected]

For the Long Numbers, I went back and switched the format to 'General' and the numbers switched from Scientific to plain old ordinary.

For the numbers with dashes, before importing change the column format to 'text' and you'll be set.

 

[cubd8]

I'm getting that division by error symbol when I use this formula below. How do I get rid of it?

=T7/(U7+V7+W7+X7+Y7+Z7)

Thank You!

 

[Pigskin Fanatic]

I'm getting that division by error symbol when I use this formula below. How do I get rid of it?

=T7/(U7+V7+W7+X7+Y7+Z7)

Thank You!

this error? #DIV/0! if so, that means the formula in red sums to zero. assuming you're using Excel 2013 or newer, use this: =IFERROR(T7/(U7+V7+W7+X7+Y7+Z7),0). it will return 0 (or whatever u put in place of the zero) if the denominator is 0 or if the formula just craps out.

 

[cubd8]

this error? #DIV/0! if so, that means the formula in red sums to zero. assuming you're using Excel 2013 or newer, use this: =IFERROR(T7/(U7+V7+W7+X7+Y7+Z7),0). it will return 0 (or whatever u put in place of the zero) if the denominator is 0 or if the formula just craps out.

Thanks, that took care of it!

 

[cubd8]

One other question, This formula is working as intended.

=IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0)

However, is there a way to add a filter to this existing logic.

IF Column T is LT 3, then output a 0, else do the above calculation?

 

[Ned]

One other question, This formula is working as intended.

=IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0)

However, is there a way to add a filter to this existing logic.

IF Column T is LT 3, then output a 0, else do the above calculation?

That's another layer that forces you to nest a couple of IFs instead of using IFERROR.

=IF(ISERROR(T30/(U30+V30+W30+X30+Y30+Z30)),0,IF((T30/(U30+V30+W30+X30+Y30+Z30))<3,0,T30/(U30+V30+W30+X30+Y30+Z30)))

 

[bcdjr1]

One other question, This formula is working as intended.

=IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0)

However, is there a way to add a filter to this existing logic.

IF Column T is LT 3, then output a 0, else do the above calculation?

=IF(T30<3,0,IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0))

 

[Ned]

=IF(T30<3,0,IFERROR(T30/(U30+V30+W30+X30+Y30+Z30),0))

 I misread his post.  

 

[Chemical X]

for the wife

got an easy one;

column A has years starting with 2017 and going higher

column B is Y or N

formula needed from column A to feed into column B - if column A is 2017 column B should be no.  if column A is > 2017 column B should be Y.

thx

 

[Psychopav]

for the wife

got an easy one;

column A has years starting with 2017 and going higher

column B is Y or N

formula needed from column A to feed into column B - if column A is 2017 column B should be no.  if column A is > 2017 column B should be Y.

thx

What should column B be if column A < 2017?

 

[Chemical X]

What should column B be if column A < 2017?

the years start at 2017 there is nothing earlier.  @Psychopav

 

[Psychopav]

the years start at 2017 there is nothing earlier.

I think it's pretty simple then. Let's say your column A has the heading "Date", and the data starts in row 2. 

In cell B2, type 

=if (year (A2)=2017,"n","y")

Copy and paste down the entire column. 

Profit. 

 

[Chemical X]

I think it's pretty simple then. Let's say your column A has the heading "Date", and the data starts in row 2. 

In cell B2, type 

=if (year (A2)=2017,"n","y")

Copy and paste down the entire column. 

Profit. 

almost, but everything says Y - 2018 2019, Etc all say Y

thought?  @Psychopav

 

[Ignoratio Elenchi]

almost, but everything says Y - 2018 2019, Etc all say Y

thought?  @Psychopav

Column A is just the years, not dates, right?  Then just do:

=if(A2=2017,"N","Y")

If that doesn't work, the other thing that comes to mind is that the years are stored as text instead of numbers or something, so you could try:

=if(A2="2017","N","Y")

 

[Psychopav]

almost, but everything says Y - 2018 2019, Etc all say Y

thought?  @Psychopav

I'm confused... Isn't that what you want?  Everything which isn't 2017 should say "y"?

 

[Chemical X]

Column A is just the years, not dates, right?  Then just do:

=if(A2=2017,"N","Y")

If that doesn't work, the other thing that comes to mind is that the years are stored as text instead of numbers or something, so you could try:

=if(A2="2017","N","Y")

so, this almost worked, but all came up as Y......so we changed the A2=2017 to A2>2017 and now it works.

thx for the push

 

[Chemical X]

I'm confused... Isn't that what you want?  Everything which isn't 2017 should say "y"?

actually, we wanted 2017 to say Y and all others to be no.

 

[Chemical X]

for the wife

got an easy one;

column A has years starting with 2017 and going higher

column B is Y or N

formula needed from column A to feed into column B - if column A is 2017 column B should be no.  if column A is > 2017 column B should be Y.

thx

sonuva - I wanted the opposite formula.

jeebus

 

[Long Ball Larry]

anybody using power pivot or power query?  useful?

 

[Chemical X]

so, wife has a pivot table (she thinks).  trying to accomplish the following;

column A has about 4 actions listed that pertain to real estate, like renew, move, extend, etc.  column B has many dates in mm/dd/yyyy form.  lets says each column has 50 rows.

so, renew might have 15 dates, move might have 10 dates, etc.

she would like to filter the dates in column B from newest to oldest, but wants to filter for each action in column A.  so, renew will filter newest to oldest, move will then filter, etc.

any thoughts or prayers?

@Ned

 

[Ignoramus]

so, wife has a pivot table (she thinks).  trying to accomplish the following;

column A has about 4 actions listed that pertain to real estate, like renew, move, extend, etc.  column B has many dates in mm/dd/yyyy form.  lets says each column has 50 rows.

so, renew might have 15 dates, move might have 10 dates, etc.

she would like to filter the dates in column B from newest to oldest, but wants to filter for each action in column A.  so, renew will filter newest to oldest, move will then filter, etc.

any thoughts or prayers?

@Ned

Not at a computer but she should be able to right-click in the values area (where dates are listed) and sort there.  It should apply the sort to each of her categories. 

 

[Chemical X]

Not at a computer but she should be able to right-click in the values area (where dates are listed) and sort there.  It should apply the sort to each of her categories. 

how would it know she has different cats to sort?  wouldn't it just sort the entire column without regard?

 

[BroadwayG]

Data|Filter seems like it might be of use for that.

If you use Data|Sort, you can add multiple levels of sorting.

 

[Ned]

so, wife has a pivot table (she thinks).  trying to accomplish the following;

column A has about 4 actions listed that pertain to real estate, like renew, move, extend, etc.  column B has many dates in mm/dd/yyyy form.  lets says each column has 50 rows.

so, renew might have 15 dates, move might have 10 dates, etc.

she would like to filter the dates in column B from newest to oldest, but wants to filter for each action in column A.  so, renew will filter newest to oldest, move will then filter, etc.

any thoughts or prayers?

@Ned

Sort the dates column first, then sort the action column.

ETA: no, your wife doesn't have a pivot table.

 

[Ignoramus]

Sort the dates column first, then sort the action column.

ETA: no, your wife doesn't have a pivot table.

Yeah, good point.  There wouldn't be dates in the values field, would there?

 

[Chemical X]

Sort the dates column first, then sort the action column.

ETA: no, your wife doesn't have a pivot table.

what makes a pivot table.....I personally have no idea.  also, sent her the suggestion.

 

[Ned]

what makes a pivot table.....I personally have no idea.  also, sent her the suggestion.

https://www.youtube.com/watch?v=Vx-Fuw46VbY

 

[Chemical X]

Sort the dates column first, then sort the action column.

ETA: no, your wife doesn't have a pivot table.

this didn't work.....

still open to ideas.

 

[Ned]

this didn't work.....

still open to ideas.

No idea what you're trying to do.  Can you put an example into a google sheet?

 

[Chemical X]

A                                                       B

Close                                            Dates

Open

Merge

so, a has only 3 values, but b has piles of dates.  she would like the dates in b that associate with a to sort by date.  so close would have 10 dates sorted, open may have 20 dates sorted, etc. 

 

[brun]

Sort by Column A, then B.

Do you care that the Merge group will appear before the Open group?

 

[Chemical X]

Sort by Column A, then B.

Do you care that the Merge group will appear before the Open group?

I don't think there is a specific order required......getting the dates to sort in their group is most important.

 

[Ned]

I don't think there is a specific order required......getting the dates to sort in their group is most important.

Like this?  https://ibb.co/cO2hWF

 

[Chemical X]

Like this?  https://ibb.co/cO2hWF

exactly......however, her sheet or pivot or whatever only has the action name listed once.

I am trying to get a copy to share, but you are on the right path.

 

[Ned]

exactly......however, her sheet or pivot or whatever only has the action name listed once.

I am trying to get a copy to share, but you are on the right path.

OK, now I understand why the sorting by B and then A didn't work.  Having those blanks screws things up (its not a good data practice).

Fill in the blanks with the appropriate actions using the auto fill feature.  Then do the sort I posted earlier.  If she insists on not having the repeated actions, she can delete them after the sorting is done.

 

[Chemical X]

OK, now I understand why the sorting by B and then A didn't work.  Having those blanks screws things up (its not a good data practice).

Fill in the blanks with the appropriate actions using the auto fill feature.  Then do the sort I posted earlier.  If she insists on not having the repeated actions, she can delete them after the sorting is done.

I sent you a PM for a look.

 

[Thorpe]

What Ned said, or highlight only the dates for one group and sort, and do that 4 times.

 

[Chemical X]

here is an easier question.  Excel work book has multiple tabs.  is it possible to email only 1 tab of a multi tabbed work book?

@Ned

 

[Long Ball Larry]

here is an easier question.  Excel work book has multiple tabs.  is it possible to email only 1 tab of a multi tabbed work book?

@Ned

right-click the tab and choose send, I believe.

Or right click and say copy to new book and save that one sheet as a new book.

 

[Ned]

Or right click and say copy to new book and save that one sheet as a new book.

This.

 

[Chemical X]

actually, neither worked.

when right-clicked, the send option doesn't come up.  when you say copy, it copies the tab in the same workbook.

 

[Chemical X]

Right click the tab,  "move or copy"

in the popup window, select "new workbook" in the drop-down menu at the top.

Then, make sure "Copy" is checked.

Then, click OK

this!

 

[Chemical X]

well,

want to put you excel nerds on alert that I will likely need 'pie chart' help later. 

proactively, any good ideas on how to easily create pie charts in excel?

tia

@Ned

 

[Peyton Marino]

has your wife considered hiring a junior analyst who actually knows how to use excel?

 

[Peyton Marino]

any chance your wife could just learn to use excel?

seems I've gone down this road before, actually