Extract row data on to new sheet, only if certain cell value is greater or less than 0

[ssmith2015]

Hello all,

Not even sure if this is possible, but I'm turning to the experts for advise.

I have sheet 1. Example table showing below (actual table has 2000+ lines). On sheet 2, I would like to extract data from sheet 1, but only select data, depending on if cell in column 0 is greater than or less than 0 (any figure except 0). Column O is a sum formula of C:N.

I would want the account #, as well as the amounts for Jan to Dec.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Total 2014[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Apr[/TD]
[TD="align: center"]May[/TD]
[TD="align: center"]Jun[/TD]
[TD="align: center"]Jul[/TD]
[TD="align: center"]Aug[/TD]
[TD="align: center"]Sep[/TD]
[TD="align: center"]Oct[/TD]
[TD="align: center"]Nov[/TD]
[TD="align: center"]Dec[/TD]
[TD="align: center"]Total 2015[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]expense 1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]140[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]expense 2[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]expense 3[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]120[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]expense 4[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]expense 5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

So as per the table above, this is what I'm hoping to see on sheet 2.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]expense 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]expense 3[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]expense 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]80[/TD]
[/TR]
</tbody>[/TABLE]

Is this at all possible? Appreciate any insight you can give. Thank you!!!

 

[hiker95]

DocAElstein,

I bow to an expert

Thank you very much, but, I am still learning - there is so much to know.....

 

[ssmith2015]

Wow! This is amazing. Thank you so very much hiker95 and thank you DocAElstein as well. You've both been a great help and a huge time saver!
One thing, I realized I made a mistake in my example, I apologize. For sheet2, I didn’t need the numbers in column A; I was trying to show the row numbers but missed a column. How can I remove those?

Now to complicate this further... although you made this sound too easy.

In the 2000+ rows, I have many subtotals. All subtotals being with the letter L and then a number(4-9), versus my regular accounts that begin with 6 digits. Example (L8_Transportation vs. 645080_Transportation Services). Is it possible to exclude any rows that the cell value in column A begin with “L”? So I only get the rows with account numbers (no subtotals).

Purpose of this is so I have a sheet with only the data that needs to be uploaded for 2015 budget.

Thank you again. This is so great.

 

[DocAElstein]

Now to complicate this further... although you made this sound too easy.

In the 2000+ rows, I have many subtotals. All subtotals being with the letter L and then a number(4-9), versus my regular accounts that begin with 6 digits. Example (L8_Transportation vs. 645080_Transportation Services). Is it possible to exclude any rows that the cell value in column A begin with “L”? So I only get the rows with account numbers (no subtotals).

Purpose of this is so I have a sheet with only the data that needs to be uploaded for 2015 budget.

Thank you again. This is so great.

Hi
. Sounds easy enough even for me!
. You simply add an additional condition.
. For example in my first code (Assuming your "L8_Transportation" is in the first colum??) , change the appropriate line to

<font face=Calibri>    <SPAN style="color:#00007F">If</SPAN> ThisWorkbook.Worksheets("Sheet1").Cells(j, 15) <> "0" And Left(ThisWorkbook.Worksheets("Sheet1").Cells(j, 1).Value, 1) <> "L" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'If your 2 conditionS is met then:</SPAN></FONT>


Or simplified

If Worksheets("Sheet1").Cells(j, 15) <> "0" And Left(Worksheets("Sheet1").Cells(j, 1).Value, 1) <> "L" Then

I checked it. It works.


. For the other stuff, it is I think just a case in my code of cutting a line out and juggling the column numbers.
. Have a go yourself! In the meantime if no one else answers (It is a bit hit and miss in this forum) then I will take a look later and write the full code out again for you. Post please in the meantime an example of sheet1 Showing a sub Total line and Sheet2 in exactly the format you want.

. Hiker95 is obviously a Pro and just now I want to go through his code as I think I may learn a lot from it once I have understood it!. His code is obviously the Professional solution so you should use that one finally, once you understand it.

Alan

 

[hiker95]

ssmith2015,

One thing, I realized I made a mistake in my example, I apologize. For sheet2, I didn’t need the numbers in column A; I was trying to show the row numbers but missed a column. How can I remove those?

Now to complicate this further... although you made this sound too easy.

In the 2000+ rows, I have many subtotals. All subtotals being with the letter L and then a number(4-9), versus my regular accounts that begin with 6 digits. Example (L8_Transportation vs. 645080_Transportation Services). Is it possible to exclude any rows that the cell value in column A begin with “L”? So I only get the rows with account numbers (no subtotals).

Purpose of this is so I have a sheet with only the data that needs to be uploaded for 2015 budget.

So that we can get it right this next time:


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[DocAElstein]

. Hiker95
Hi.
. Copied, tried and went through your codes and ruined them with green comment “graffiti” until I understood it all. Took a while because I am just a Part-time Ammeter. But there is a lot of good typical range stuff that was good to learn. (Be using some stuff from that in my http://www.mrexcel.com/forum/excel-questions/792647-simple-data-sort-merge-code.html later). I need to get a good grasp of finding end of tables and re dimensioning things to keep a code flexible , efficient and therefore Professional!! Have to be careful though, end of files stuff does not always seem to be well behaved. You can get caught out I think if you clear a part of a file rather than deleting the rows. Last line things can catch you out. Still fighting a bit with that one ( http://www.mrexcel.com/forum/excel-...%3D-y-%96-1-usedrange-rows-count-anomale.html ). I think last Row can sometimes be the last row you used even if it is empty or “cleared”? So then that might get un expected results with the “ .End(xlUp) “ thing?

. One quick question. This “ .End(xlUp) “ thing crops up a lot. I am trying to get a really good grasp of the basics: Would you call that a method or Property?. F1 and F2 don’t seem to clear on this one. (Andrew Poulsum told me the Help in later XL versions is not too good.. I have 2007 and 2010 – had 2003 but the old computer just died!!)
Thanks
Alan.

 

[hiker95]

DocAElstein,

I am a little confused as to your last thread?

How about not hijacking this thread with a lot of questions.


You have two large paragraphs with a lot of different questions, and, links to other threads?

How about creating you own NEW threads (each new thread for one set of data), with:

1. A clear and descriptive title, like, Help with understanding: finding end of tables and re dimensioning things to keep a code flexible , efficient

2. Screenshots of your working raw data

3. Screenshots of your results

4. Post your macro code

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]


Then, for each new thread, send me a Private Message with a link to your thread, and, I will have a look.

 

[DocAElstein]

DocAElstein,

I am a little confused as to your last thread?

How about not hijacking this thread with a lot of questions.


You have two large paragraphs with a lot of different questions, ....

Hi,

OK, Sorry - still a bit new here!

Thanks.
Alan

 

[hiker95]

DocAElstein,

You are very welcome.

Come back anytime.

 

[DocAElstein]

Ssmith 2015,
.. How did you get on? Any Feedback?