Moving Rows to Separate Workseheets by Cell Value

[diderooy]

Good morning/afternoon!

I see a lot of similar requests out there about moving rows based on a "Yes" value entered by the user. However, my quick search only showed data moved from one worksheet to another. I apologize for asking this question again, and might be able to work off another thread's answer if someone could point me to it.

In my case, I have already created a macro for identifying my data, boiling a lot of variables down to an acronym named in a single cell, in column A. I have 13 different items in my "acronym" column: CTO, CSO, CLN, RTO, RSO, RLN, NRTO, NRSO, NRLN, FALSETO, FALSESO, FALSELN, and 0 (the 0 is for all the rows that have only supporting documentation, but aren't independent accounts...couldn't figure out how to get rid of that).

Here is a sample of what I'm left with:
[TABLE="class: grid, width: 1108"]
<tbody>[TR]
[TD="align: left"]FALSELN
[/TD]
[TD="align: left"]195671-000
[/TD]
[TD][/TD]
[TD="align: left"]ISABEL HERRERA
[/TD]
[TD][/TD]
[TD="align: right"]65.78
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]UNKNOWN
[/TD]
[TD][/TD]
[TD="align: left"]35 4TH ST N
[/TD]
[TD][/TD]
[TD="align: right"]25.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]760 Meter No: 52939 Container
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]274
[/TD]
[TD="align: right"]90.78
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]NRLN
[/TD]
[TD="align: left"]195881-000
[/TD]
[TD="align: left"]1150803
[/TD]
[TD="align: left"]NICK GALLEY
[/TD]
[TD][/TD]
[TD="align: right"]171.04
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]NON RESIDENTIAL
[/TD]
[TD][/TD]
[TD="align: left"]145 4TH ST N
[/TD]
[TD][/TD]
[TD="align: right"]25.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]760 Meter No: 13436 Container
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]26967
[/TD]
[TD="align: right"]196.04
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]NRSO
[/TD]
[TD="align: left"]185972-000
[/TD]
[TD="align: left"]1150604
[/TD]
[TD="align: left"]TONY WILKINS
[/TD]
[TD][/TD]
[TD="align: right"]139.93
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]NON RESIDENTIAL
[/TD]
[TD][/TD]
[TD="align: left"]151 12TH ST N
[/TD]
[TD][/TD]
[TD="align: right"]25.00
[/TD]
[TD="align: left"]06/09/14
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]760 Meter No: 22133821260 Container
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]16496
[/TD]
[TD="align: right"]164.93
[/TD]
[TD="align: left"]04/07/14
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]02/03/14
[/TD]
[/TR]
[TR]
[TD="align: left"]RSO
[/TD]
[TD="align: left"]185408-000
[/TD]
[TD="align: left"]2211703
[/TD]
[TD="align: left"]MARGARITE HAAL
[/TD]
[TD][/TD]
[TD="align: right"]77.27
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]Residential
[/TD]
[TD][/TD]
[TD="align: left"]154 7TH ST N
[/TD]
[TD][/TD]
[TD="align: right"]25.00
[/TD]
[TD="align: left"]06/17/14
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]760 Meter No: 13382 Container
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]71473
[/TD]
[TD="align: right"]102.27
[/TD]
[TD="align: left"]06/09/14
[/TD]
[/TR]
[TR]
[TD="align: left"]RLN
[/TD]
[TD="align: left"]015203-000
[/TD]
[TD="align: left"]1152603
[/TD]
[TD="align: left"]FRANCISCO MARTINEZ
[/TD]
[TD][/TD]
[TD="align: right"]80.06
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]RESIDENTIAL
[/TD]
[TD][/TD]
[TD="align: left"]75 13TH ST N
[/TD]
[TD][/TD]
[TD="align: right"]25.00
[/TD]
[TD="align: left"]04/15/13
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]760 Meter No: 13383 Container
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]10015
[/TD]
[TD="align: right"]105.06
[/TD]
[TD="align: left"]08/06/12
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]12/12/11
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]04/11/11
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]01/31/11
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]10/04/10
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]08/09/10
[/TD]
[/TR]
[TR]
[TD="align: left"]NRSO
[/TD]
[TD="align: left"]015239-000
[/TD]
[TD="align: left"]1153801
[/TD]
[TD="align: left"]JEREMY KIRKKEDAHL
[/TD]
[TD][/TD]
[TD="align: right"]94.05
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]NON RESIDENTIAL
[/TD]
[TD][/TD]
[TD="align: left"]36 4TH ST S
[/TD]
[TD][/TD]
[TD="align: right"]25.00
[/TD]
[TD="align: left"]06/09/14
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]760 Meter No: 13381 Container
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]4492
[/TD]
[TD="align: right"]119.05
[/TD]
[TD="align: left"]04/09/12
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]10/04/10
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]06/08/10
[/TD]
[/TR]
[TR]
[TD="align: left"]NRLN
[/TD]
[TD="align: left"]015280-000
[/TD]
[TD="align: left"]1154905
[/TD]
[TD="align: left"]LINDA MILNER
[/TD]
[TD][/TD]
[TD="align: right"]97.38
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]NON RESIDENTIAL
[/TD]
[TD][/TD]
[TD="align: left"]311 MEREDITH ST
[/TD]
[TD][/TD]
[TD="align: right"]25.00
[/TD]
[TD="align: left"]06/06/11
[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]760 Meter No: 13437 Container
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]4300
[/TD]
[TD="align: right"]122.38
[/TD]
[TD="align: left"]06/08/10
[/TD]
[/TR]
</tbody>[/TABLE]

I need a separate worksheet for each acronym except for the zero, where the acronym and all other information from the corresponding row would be moved to--based on my sample, the single row for Herrera would be in a worksheet of FALSELN with other FALSELNs, single row for Galley would be in NRLN with others, and single row for Wilkins would be in NRSO with other NRSOs, etc. Surely there is a way to do this, isn't there?

 

[hiker95]

diderooy,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

3. Does your raw data contain titles in row 1?

4. What cell in your screenshot contains the first FALSELN?


Can we have screenshots of the resulting worksheets RLN, and, NRSO, based on the data in your screenshot?

 

[diderooy]

Thanks, hiker, for asking--

1-2.) PC, Windows 7, Excel 10.

3.) My report contains a title on row 1 (leftover from the report), a subtitle in row 2 (ditto), and a manually-entered reference date into A1 which helps determines the acronym (recent history, seen in the last column, affects our department's action taken on each account). The type of data given in the sample above begins in row 3, after deleting entirely-blank rows. However, I'm not dead-set on my current format.

4.) The first FALSELN occurs in cell A100 in this report, but this will vary--I am modifying reports regarding a municipality's water bills, and this report only lists past due accounts, so any acronym could occur on the first exported row.

Sorry, I should have been more clear about this--I haven't yet produced any "filtered" worksheets, I was only trying to describe my goal. The goal is to have the main spreadsheet split into 12 separate reports, one for each of the acronymns above, containing all data from any row that begins with the corresponding acronym (and excluding the "0" rows).

 

[diderooy]

I apologize, I just realized my sample was incomplete--all cells currently blank in column A have a "0" in them. These rows, to make sure I'm clear, are not needed, only the ones with other characters.

Within my Excel worksheet, these 0s are still there, but if I copied and pasted directly, and the 0s didn't convert, perhaps that means that the VBA wouldn't have to account for them? I'm not sure...

 

[hiker95]

diderooy,

It would appear from your directions/descriptions that screenshots/text displays will not work.

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

I will have to see your actual workbook/worksheet, with the resulting worksheets manually formatted by your for the results you are looking for.

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.


If you are not able to provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[diderooy]

File available here:

https://app.box.com/s/iy1zkatmdsp6cjydtxwj

Thanks much!

 

[hiker95]

diderooy,

Thanks for the workbook.

One last try so that we can get it right:

1. Can we have another workbook with the raw data in worksheet Sheet1 (2), per your latest posted workbook on BOX?

2. And, can we have the resulting worksheets RLN, and, NRSO, manually formatted by you for the results you are looking for?

 

[diderooy]

Hiker,

1. You just want another copy uploaded, identical to the one before? DONE @ 4:50
2. I didn't actually produce any worksheets for RLN or NRSO yet, I was only referring to my goal with this request/thread. You're wanting me to manually copy and past into a new worksheet, so you have something to check the VBA against? DONE @ 4:50

*Sorry if I'm not understanding you correctly--I think I'm misunderstanding #1, but can't imagine what else you're asking me...

 

[hiker95]

diderooy,

We seem not be able to understand each other.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[diderooy]

Bump