Ask me anything about Microsoft Excel - Page 63 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

Gospy · 07-18-2012, 12:49 PM

Quote:

Originally Posted by TJay

I have 2 identical sheets that are currently sorted from 1 to 1000 in column A, with other info in column B.

On sheet A I put on an auto filter, and filter column B for "contains x" (this reduces my view to only seeing 400 lines.)

I add in a new Column of information for all 400 lines (column C) on sheet A.

I now sort sheet B so that it only shows the same 400 lines in the same order.

I copy column C from sheet A, and want to paste it into sheet B so that it obviously matches up with the 400 lines that contain X.

When you paste this, it will screw the sheet up because it will ignore the "contains" filter when you paste, and will end up getting the 400 lines of new info in all of the wrong rows.

How can you make it just paste to the 400 rows you can see? This is something I encounter about twice a day at work. I've obviously simplified it (usually the information I need to paste is from 2 completely different spreadsheets, so I can't simply filter them to be the same), and it would save me a ton of time if there was a shortcut for having to copy/paste line by line.

Thanks

Is column A full of unique values? (like a PO or something) If not could you make a column of unique identifiers?

Then it seems like a simple vlookup to me, perhaps with an iferror around it to account for errors. I wouldn't copy paste things like that anyway, try to find ways to reduce human error to 0.

Gospy · 07-18-2012, 01:13 PM

Quote:

Originally Posted by Josem

Hi,

I have Column A (over 90,000 rows) with values that I want to graph in an ordinary line chart. Excel is limited to 32,000 values in a line chart.

Thus, I'd like Column B to reference every 500th value in Column A:

A1
A501
A1001
... and so on

Is there a way to do this automatically with a formula of some sort?

If this doesn't need to be dynamic I can think of a less than elegant way to do this using index.

Josem · 07-18-2012, 01:32 PM

Yeah - that seems to be fine.

I have used the formula here to solve it:

http://www.excelforum.com/excel-gene...worksheet.html

(adjusting from 7 to my desired frequency)

Justine Bieber · 07-18-2012, 01:32 PM

question, is there a formula or macro that can be programed to

"If cell A does not contain these specific words "this", "or", "That", "oranage", "hat", "etc", then delete that row/column"

And by delete i mean to remove the row/column completely, not just delete the text in it.

hope you can help!

Spurious · 07-18-2012, 02:48 PM

Quote:

Originally Posted by Your Mom

I have three column of data I am putting into a pivot table. The first column is a bunch of point spreads, the second column is a bunch of totals, the third column is either TRUE or FALSE. I want a pivot table that breaks TRUE/FALSE results down first by point spread and then by a range of totals.

So for example, it would be for point spreads of -3, then it would give me TRUE/FALSE results for totals below 45, totals between 45.5 and 60, and totals above 60. Any way to do that? Hopefully, this makes sense to someone.

Not sure if this is possible to do with pivots (it might be).
But, why dont you do a 4th column categorizing all the totals?
=IF(B1<45.5,"<45",IF(B1>60,">60","45.5 - 60"))
Then display this in the pivot instead of the second column.

Spurious · 07-18-2012, 02:55 PM

Quote:

Originally Posted by Justine Bieber

question, is there a formula or macro that can be programed to

"If cell A does not contain these specific words "this", "or", "That", "oranage", "hat", "etc", then delete that row/column"

And by delete i mean to remove the row/column completely, not just delete the text in it.

hope you can help!

sub DeleteStupidWordRows()
dim i as long

Quote:

for i=Activesheet.usedrange.rows to 1 step -1
if instr(1,activesheet.cells(i,1),"this") < 1 AND instr(1,activesheet.cells(i,1),"or") < 1 AND... then
activesheet.cells(i,1).entirerow.delete xlshiftup
end if
next i

This should do the trick.
Havent tested it though.

TJay · 07-18-2012, 06:49 PM

Quote:

Originally Posted by Gospy

Is column A full of unique values? (like a PO or something) If not could you make a column of unique identifiers?

Then it seems like a simple vlookup to me, perhaps with an iferror around it to account for errors. I wouldn't copy paste things like that anyway, try to find ways to reduce human error to 0.

Nope, usually I will send off a list of items with a description, and the list will comeback with similar (yet always formatted differently) descriptions, and a column of information applied to each line (like a price). Only parts of the list need this info, so its always sorted.

Gospy · 07-18-2012, 07:23 PM

So there's information about items that you don't see(filtered out but still match both sheets) that you don't want in your sheet? And items aren't unique?

If that's true, again not so elegant, but you can sort by "contains x" or whatever condition you have and the first x entries will match up and you can c/p.

TJay · 07-19-2012, 12:46 AM

Quote:

Originally Posted by Gospy

So there's information about items that you don't see(filtered out but still match both sheets) that you don't want in your sheet? And items aren't unique?

If that's true, again not so elegant, but you can sort by "contains x" or whatever condition you have and the first x entries will match up and you can c/p.

Imagine if I send out a list of 400 items (off my original list of 1000, in completely random order) that say "Ace - Clubs" "Ace in Diamonds" "A of H" "2 of diamonds" etc for 400 lines.

They come back with

Club,Ace
Diamond,Ace
Heart,Ace
Diamond,Two

With additional information in another column for each card. I obviously can't match them up because the cards are described different ways, so V lookups are out of the question. However they are still in the same order 99% of the time. so if I could copy paste the whole list it would be perfect. Otherwise I have to copy paste line by line.

I hope I'm explaining this right.

Gospy · 07-19-2012, 03:21 PM

Oh ok I see. Yea, sorry, I just misunderstood. It seems like you need some sort of key. Like just 2 columns: My wording | Their wording. Though I can see this being a problem with multiple vendors/or if it just comes back different on separate tries. I have this problem on occasion myself though and I just make a key and use it for all future mapping.

If you can filter on your condition, perhaps try sorting on it instead of filtering. This way the entire list is exposed but all of your "contains x" items are grouped at the top. If it can't be done with a regular sort maybe use conditional formatting on "contains x" and then sort by color. Seems like this is bound to be tedious any way you do it. Sorry I couldn't help more.

Spurious · 07-19-2012, 04:15 PM

Can you write something in column C on the original sheet marking it? Something like an "x".

Then you could write in cell D1 of the original sheet:
"=INDEX(SheetB!C:C,COUNT2($C$1:$C1),1)" and drag it all the way down.

Your Mom · 07-19-2012, 05:03 PM

Quote:

Originally Posted by Spurious

Not sure if this is possible to do with pivots (it might be).
But, why dont you do a 4th column categorizing all the totals?
=IF(B1<45.5,"<45",IF(B1>60,">60","45.5 - 60"))
Then display this in the pivot instead of the second column.

Spenda · 07-20-2012, 02:50 PM

grunching big time

I need an IF equation that changes a negative number into a positive one

so If Cell B2 = S then I want the result in H2 to be multiplied by -1, IF B2 = L then nothing should happen

lil help?

Tappy Tibbons · 07-20-2012, 03:46 PM

Quote:

Originally Posted by Spenda

grunching big time

I need an IF equation that changes a negative number into a positive one

so If Cell B2 = S then I want the result in H2 to be multiplied by -1, IF B2 = L then nothing should happen

lil help?

Are you saying there's a formula that gives the result in H2, and you always want it to be positive? If so, in H2 type =ABS(whatever formula is there currently)

knivesout · 07-20-2012, 03:49 PM

=IF(B2="S",H2*(-1),H2)

Paste that in I2, hide column H if you don't want to see both columns of numbers.