Looking for a Programmer

[Lord Iames Osari]

Hey, I need the assistance of a programmer for a thingy at work.

I won't go into exact details of why we need this, but I can tell you what we need. But first, a sample spreadsheet.

{table="head"]
A|
B|
C|
D|
E

1|00001|Someplace St|House 1|Apt 1|~
2|00001|Someplace St|House 1|Apt 2|~
3|00001|Someplace St|House 2|~|~
4|00001|Someplace St|House 3|~|~
5|00001|Elsewhere Ln|House 4|Apt 3|~
6|00002|Someplace St|House 5|~|~[/table]

What we need is a program that can go into a spreadsheet like the one above and find all the 00001 values in column A, then find all the Someplace St values in column B for which column A has a value of 00001 (in our sample, cells A1:B4), then select in those rows out to column D and copy-paste into a new spreadsheet.

Can this be done? And can anyone do it for us and send me the program? We use Excel for our spreadsheets.

[mikeejimbo]

Given that it's Excel, someone can probably do something nifty with a Macro. If I had Excel on here I'd try to mess with it, but alas, I do not.

Besides which, I would assuredly be beaten to the punch by people better than I.

[Rawhide]

You can do this manually with Excel's "Filter & Sort", just have it filter the results to only show those two.

How many documents do you need to sort?

[Lord Iames Osari]

Oh, I know this all can be done manually. That's what we've been doing up till now. The problem is that doing it manually is time consuming. That's why we're looking into the possibility of automating the process.

[Rawhide]

Oh, I know this all can be done manually. That's what we've been doing up till now. The problem is that doing it manually is time consuming. That's why we're looking into the possibility of automating the process.

If you know a little bit of vbscripting, record a macro of you doing it manually, then you can edit it slightly to ask you what to sort.

[Ashtar]

Assuming you have a named range "NamedRange" which goes over the whole table... do something in a macro like:


Sub a()
Dim Filter1 As Variant
Dim Filter2 As Variant
Dim Range1 As Range
Dim Range2 As Range

Filter1 = InputBox("Give value A")
Filter2 = InputBox("Give Value B")

Set Range1 = ThisWorkbook.Names("NamedRange").RefersToRange
'Field2 is column B
'Field3 is column C
Range1.AutoFilter Field:=2, Criteria1:=Filter1, Operator:=xlAnd
Range1.AutoFilter Field:=3, Criteria1:=Filter2, Operator:=xlAnd

Dim r As Integer
Dim targetCount As Integer
Dim rCount As Integer
Dim targetArea() As Variant
Dim iCol As Integer
Dim iColCount As Integer
iColCount = Range1.Columns.Count
rCount = Range1.Rows.Count
targetCount = 1

'Could be optimized by giving the correct dimentions with a precount
ReDim targetArea(1 To Range1.Rows.Count, 1 To Range1.Columns.Count)
'Avoiding row 1 which is title row
For r = 2 To rCount
'Is this row currently visible ?
If (Range1.Rows(r).Height > 0) Then
For iCol = 1 To iColCount
targetArea(targetCount, iCol) = Range1.Cells(r, iCol)
Next iCol
targetCount = targetCount + 1
End If
Next r

'We have a variantArray targetArea filled with our stuff
'Show how many rows we took
MsgBox targetCount - 1

'Now we need to paste the variant array to another range on another sheet
'Left as an exercise in VBA ^^
End Sub


This auto filters your list, and fills a variant array in memory with the data. Now you just need to paste this targetArea to a range in another sheet.

Hope this helps, if ever post again. I'm going for lunch now.

[Lord Iames Osari]

If you know a little bit of vbscripting, record a macro of you doing it manually, then you can edit it slightly to ask you what to sort.

My knowledge of programming extends not much far beyond, "People can type up stuff that makes computers do stuff," and making Excel do math for me.


Assuming you have a named range "NamedRange" which goes over the whole table... do something in a macro like:

*snip*

This auto filters your list, and fills a variant array in memory with the data. Now you just need to paste this targetArea to a range in another sheet.

Hope this helps, if ever post again. I'm going for lunch now.

I appreciate however much effort it took for you to come up with all that, but I don't really understand any of it. I believe you that it would work if I knew how to implement it, but I don't. :smallredface:

[Ashtar]

Okay, will try to either do a workbook with my code and a complete working example later today or tonight. I hope it's not super urgent, 'cause I got lots to do today.

Anyway, to get a Named range in excel, you select an area on your workbook and look to your upper left on excel (Excel 2003), there is a white box with the code of the first box selected in the range. (Eg, if I select D7 to E10, the box shows 4R x 2C while I'm selecting and then shows only D7 after I stop clicking)
With a range selected, simply click on the white box and type "NamedRange" into the white box. Validate with enter.
Now at the edge of the white box, you get a drop down arrow, when you have a named range in your workbook, you can automatically select the whole range by going to the drop down box and clicking on it's name in the drop down list.
These named ranges are really useful when you do excel programming since they allow you to refer to areas on your sheet without the program having to be adjusted if you decide to move the area.

Second part, to access the VBA component, you can simply press Alt+F11, this opens the VB editor.
Double click on "Sheet1 (Sheet1)" should bring up an empty text area.
In there you can add VBA code to be exectued.
For example copy the code below to that area

Public Sub Show_a_Message()

MsgBox "Hello World"

End Sub

To execute this, just click anywhere in the code between Public Sub and End Sub and press the F5 key to execute the VB macro.

You can also launch VB macros from the Tools -> Macro -> Macros... menu in Excel. The shortcut is Alt+F8 on my version.

Hope this helps and I'll see if I can do something more later.

[Lord Iames Osari]

Ah, nifty.

It's not super-urgent, but we'd like to have it as soon as we can.

[Lorn]

If I was thinking more clearly, I'd get something set up quickly now. Having said that:

You say you do it manually. How do you do this?

If you're actively using Excel to find them instead of simply looking through, record a macro.

Tools>macro>record.

Then just go through the process.

At the end, click the blue button on the toolbar-y thing that appeared when you pressed Record.

Then you can go to tools>macros>run macro and select the macro to run.

Any help?

[Lord Iames Osari]

We use the Find feature to find the cell ranges we need, select them, then copy-paste them into a document.

The problem with using a macro (at least, as far as my macro-making skills are concerned) is that every time we do this, we're looking for different ranges of data, so we need something that will let us tell Excel what to look for, then set it loose and let it take care of finding and copying the data for us.

[Ashtar]

That's where the input boxes and the variables come into play. I'm away from excel tonight, and nursing much too much whiskey...
Hopefully I'll get something done on the train tomorrow and post it from my mum's.

[SMEE]

I did something akin to that before. I'll post the MS Excel 2003 macro here in a few hours, tops.

Edit: Forget about posting macros. Here is the excel spreadsheet

http://www.smeenet.org/filterandcopy.xls

It uses columns H1 and J1 for the columns B and C auto filter values.

Have fun!

[Lord Iames Osari]

Hey, thanks, SMEE.

I've played around with it a bit, and there are a couple of flaws I need to point out.

Firstly, we need the info in Column E as well. Currently, it's not getting copied along with the rest.

Secondly, whenever I try to run the macro searching for "2" and "Someplace St", the resulting workbook copies only the little header things labeled A, B, C, and D.

And finally, I'm not sure exactly how many cells will need to be sorted at any one time, but it goes well into the ten thousands. Is there a way to adapt your macro to work on a (potentially) infinite number of cells?

[SMEE]

Well, your first post said that collumns from A to D would be copied.

Regarding your second point, I'll see what the auto filter is doing.

And third, it'll work for up the max row limit you can fill in a given MS Excel version minus one (due to the header). 65534 rows for MS Excel 6 to 2003.

Edit: Spreadsheet fixed to pick up column E and fixed the small problem regarding ignoring the last line at the filtered range. I forgot I was copying the title row as well... :smallredface:
You can pick it up at the same place.

[Lord Iames Osari]

I misspoke earlier. I also screwed up the table. But thank you.

[Lord Iames Osari]

And third, it'll work for up the max row limit you can fill in a given MS Excel version minus one (due to the header). 65534 rows for MS Excel 6 to 2003.

I just tried this (adding more data to be sorted in row 287) and it didn't work. I'm guessing there's something in the macro program I need to change. What is it?


Sub FilterAndCopy()

'Declaring our beloved variables!
Dim Rng As Range
Dim WrkBook As Workbook
Dim ThisWrkBook As Workbook

'Activate the autofilter using the specified values at H1 and J1
Sheets(1).Range("A1").AutoFilter field:=2, Criteria1:=Range("H1").Value
Sheets(1).Range("A1").AutoFilter field:=3, Criteria1:=Range("J1").Value

'Keep reference to this workbook
Set ThisWrkBook = ActiveWorkbook

'Now that we have the range activated, select the active cells
With Sheets(1).AutoFilter.Range
'VB error trapping sucks, but we have to work with the tools that were given to us
On Error GoTo Error
Set Rng = .Offset(0, 0).Resize(.Rows.Count, 5) _
.SpecialCells(xlCellTypeVisible)
'Clear the error trapping as it's no longer needed
On Error GoTo 0
'We are done selecting the valid cells
End With

'If we have cells in our range, dp
If Not Rng Is Nothing Then
'Copy them
Rng.Copy
'Create a new workbook
Set WrkBook = Application.Workbooks.Add()
'Activate it so we can paste the copied values to it
WrkBook.Sheets(1).Activate
'Select the first cell at the first worksheet at our new work book so Excel will know where to paste
Range("A1").Select
'Paste the content to the new worksheet
WrkBook.Sheets(1).Paste
'Clear the copy selection
Application.CutCopyMode = False

End If

Error:
'Return to our workbook and clear the filters
ThisWorkbook.Activate
Sheets(1).Activate
Sheets(1).Range("A1").AutoFilter field:=3
Sheets(1).Range("A1").AutoFilter field:=2

End Sub

[SMEE]

Nope. The macro works perfectly. I just tried an example with 3327 lines of data.

What is probably going on is that you have one or more empty lines between the last row of data and row 287, and that makes row 287 to not be a part of the filtered data set that the autofilter is applied upon.

You must not have empty lines between datarows or they won't be a part of the autofilter range.

[Lord Iames Osari]

Okay :smallredface:

Edit: Also, would it be possible to adopt the macro so we don't need that header line there with the drop-down menus? And also to get rid of the numbers at the far left? The data sample to be sorted actually looks more like this:

{table="head"]
~|
A|
B|
C|
D

1|00001|Someplace St|House 1|Apt 1|~
2|00001|Someplace St|House 1|Apt 2|~
3|00001|Someplace St|House 2|~|~
4|00001|Someplace St|House 3|~|~
5|00001|Elsewhere Ln|House 4|Apt 3|~
6|00002|Someplace St|House 5|~|~[/table]

Where the letters are the column labels on the spreadsheet and the numbers are the row labels.

[SMEE]

To remove the drop down menus at the header, go to the data menu, select filter and uncheck auto-filter.

Now, to not copy the A column, you have to make the following change at the macro



Set Rng = .Offset(0, 0).Resize(.Rows.Count, 5) _
.SpecialCells(xlCellTypeVisible)


to



Set Rng = .Offset(0, 1).Resize(.Rows.Count, 4) _
.SpecialCells(xlCellTypeVisible)


And then it'll copy only columns B to E.

Edit:
That's, of course, considering that we keep the data at the same structure.
To the structure you gave right now, other change has to be done.
I'll post it soon.

Another edit: (Yay, code)



Sub FilterAndCopy()

'Declaring our beloved variables!
Dim Rng As Range
Dim WrkBook As Workbook
Dim ThisWrkBook As Workbook

'Activate the autofilter using the specified values at H1 and J1
'field:=1 == Column A
Sheets(1).Range("A1").AutoFilter field:=1, Criteria1:=Range("H1").Value
'field:=1 == Column B
Sheets(1).Range("A1").AutoFilter field:=3, Criteria1:=Range("J1").Value

'Keep reference to this workbook
Set ThisWrkBook = ActiveWorkbook

'Now that we have the range activated, select the active cells
With Sheets(1).AutoFilter.Range
'VB error trapping sucks, but we have to work with the tools that were given to us
On Error GoTo Error
'We only have 4 columns, so we only copy 4 columns
Set Rng = .Offset(0, 0).Resize(.Rows.Count, 4) _
.SpecialCells(xlCellTypeVisible)
'Clear the error trapping as it's no longer needed
On Error GoTo 0
'We are done selecting the valid cells
End With

'If we have cells in our range, dp
If Not Rng Is Nothing Then
'Copy them
Rng.Copy
'Create a new workbook
Set WrkBook = Application.Workbooks.Add()
'Activate it so we can paste the copied values to it
WrkBook.Sheets(1).Activate
'Select the first cell at the first worksheet at our new work book so Excel will know where to paste
Range("A1").Select
'Paste the content to the new worksheet
WrkBook.Sheets(1).Paste
'Clear the copy selection
Application.CutCopyMode = False

End If

Error:
'Return to our workbook and clear the filters
ThisWorkbook.Activate
Sheets(1).Activate
Sheets(1).Range("A1").AutoFilter field:=2
Sheets(1).Range("A1").AutoFilter field:=1

End Sub


And it's done.

[Lord Iames Osari]

Okay. I think I'm still not quite getting my point across, but I think I can work around that.

[SMEE]

Well, my last edit cover the new data structure you posted. :smallsmile:

That shall do it.

[Lord Iames Osari]

Okay, good, because what I said before about working around it? Totally wasn't happening.

[Lord Iames Osari]

Wombat! I tried running the macro and all it did was copy the contents of the top row. And screw up the original spreadsheet.

Edit: to be exact, it deletes everything in the original spreadsheet except the top row, which it turns into those autofilter drop-downs.

[SMEE]

I just found out why.



Sheets(1).Range("A1").AutoFilter field:=3, Criteria1:=Range("J1").Value


Should be



Sheets(1).Range("A1").AutoFilter field:=2, Criteria1:=Range("J1").Value


To "fix" the spreadsheet, turn autofilter on and clear column C filter and apply the code change to the macro. I let that field:=3 slip. :smallfrown:

[Lord Iames Osari]

... It's still doing it. :smallmad:

If it works for you, then I must be doing something wrong. But I don't have the knowledge to know what it is that I'm doing wrong. :smallfurious:

[SMEE]

http://www.smeenet.org/filterandcopy20.xls

There.
You probably removed the A column, and that made the filter rows H1 and J1 to be dislocated to G1 and I1, and the macro is still looking for H1 and J1.

[Lord Iames Osari]

Ok, I'll try that out in a bit. If I can't make it work, well, I've gotten copies of the spreadsheets we need this for, and I'll send them to you so you can design a macro based on the spreadsheets themselves, rather than the vague approximations I've been providing.

[Ashtar]

Set Rng = .Offset(0, 0).Resize(.Rows.Count, 5) _
.SpecialCells(xlCellTypeVisible)



Ooh nice code SMEE. I'll have to remember that SpecialCells function. I've always been doing it on Row.Height > 0.

[Lord Iames Osari]

Just got back from a brief vacation, where I completely forgot to fiddle with the new macro. I'll take a look in a little bit and report back.