Apache OpenOffice Community Forum

Hey OO team,

I am trying to figure out how to best copy entire rows of data to a seperate worksheet based on a specific value being met.

In this case I run a medical practice and I want to be able to create seperate worksheets detailing all appointments by specific practitioner. This will then form the basis of creating different KPI's based on the other data in the rows

I currently do this manually but it takes hours as we see many (,000's) per KPI period. We also do 3 month rolling averages and I would like to be able to input new data as we see more clients into the first sheet and then have automatic updating through the subsequent sheets

1) is there a formula that will allow me to do this? I have tried V and HLOOKUP and INDEX/MATCH but they all seem to have limitations.

2) From doing some research it seems as though a Macro is the way to go - I have absolutely 0 experience with macro's and if this is the only way to go some step by step instructions would be greatly appreciated!!

Thanks in advance. Here is a sample of the spreadsheet that I am dealing with....please note all identifying information has been changed to Seinfeld (well most of it) names.

Demo spreadsheet attached.

GCT

It can be done with formulae but it seems like a waste of effort when the same result is achieved with an AutoFilter on column D.
Link is to a Mediafire file with both methods.
https://www.mediafire.com/?cwc2j5fv5y2ar5y
On each practitioner's sheet the formulae in columns C to Q refer to the results of a Helper formula in column B which in turn refers to another helper formula in column A.
The formulae have been filled down to only row 1002 so can only process 1000 rows from the Data sheet. The formulae in columns A to Q will need to be filled down further to process more than 1000 rows from the Data sheet.

Ken Johnson

Good day
Im trying to achieve something very similar for sorting out a CCG collection, just a little simpler i think...

If you look at the attached file you can see what i have so far... it reads count vs max and outputs a yes or no if complete, then tells me how many I have extra for trade.

I want to now do a check on if it is complete and I have trades (so if Column J shows "yes" AND the For Trade number equal to or higher than 1 in column K)

I want it to then take that row and put it in a new sheet in the next available open row

maybe as per attachment... Ken Johnson

snake eyes wrote:(so if Column J shows "yes" AND the For Trade number equal to or higher than 1 in column K)
I want it to then take that row and put it in a new sheet in the next available open row

menu Date > Filters > Standard Filter will do that if you also click More, check Copy to, and specify a position on a different sheet to display the results. Problem is that's all it will do. To keep track of changes in your collection going forward as you make more trades you'd have to enter everything twice.


There are a few ways to get to where you're looking to go; still learning myself here but I think they're all going to require another column be added. Tends to be the starting point to the solution more often than not. Anyway, right click K header > Insert Column, call it something like Surplus, fill =IF(L2>=1;"YES";"NO") down from K2, right click K again > Hide if desired, hit an arrow key, and here's two more options:

FIrst is a pivot table. With this setup you can use the existing table for editing the data whenever your collection changes and the pivot table for viewing that data in whatever configuration gives you the best overview of what's what. Right click > Edit Layout on the one in the test file and try swapping Complete & Surplus, ticking more boxes, and/or later dragging in Type & Affiliation.

Second is menu Data > Filter > AutoFilter and using the dropdowns on Complete and Surplus to switch between the two layouts as needed. This can be made to work without the extra column, just not well. Third table in your test file to see what I mean. Better to have a Surplus column and streamline the toggle with Shift + Right from a cell in Complete > menu Data > Group and Outline > Group > Columns