Excel formula help

[Homerboy]

I have a table in a sheet "FILES"

FILENO	CODE	USER	DATE	COMMENT	DATE_RECD	BALANCE
640903	108	25	2011-01-26	aag notes	12/05/2010	5564.51
635932	108	23	2011-02-05		10/17/2010	1906.72
611245	99	25	2011-02-12		04/11/2010	8237.08
638622	99	12	2011-02-13		11/14/2010	12406
603839	91	25	2011-03-10		03/28/2010	6414.03
649496	99	15	2011-03-28		02/15/2011	2995.15
639838	91	16	2011-03-29	RETAINED?	11/28/2010	1477.91

What I want to happen is on additional sheets, one named for each type of code (lets say 90-110, so 20 sheets total) have all the rows that apply to that CODE.

So on sheet 108, I'd get the following rows:

FILENO	CODE	USER	DATE	COMMENT	DATE_RECD	BALANCE
640903	108	25	2011-01-26	aag notes	12/05/2010	5564.51
635932	108	23	2011-02-05		10/17/2010	1906.72

On sheet 99 I'd get the following rows:

FILENO	CODE	USER	DATE	COMMENT	DATE_RECD	BALANCE
611245	99	25	2011-02-12		04/11/2010	8237.08
638622	99	12	2011-02-13		11/14/2010	12406
649496	99	15	2011-03-28		02/15/2011	2995.15

So on and so forth (I have 1000s of rows in the FILES sheet and need to separate them based on the CODE to individual pages.
Make sense?

 

[Ken g6]

For the love of all that is holy, please stop using excel as a database program.

Get rid of excel and use a database program.

Upgrade to Access.

[runs]

This! [runs]

[runs back]OK, maybe you could do something with a pivot table; I'm not sure. [runs again]

 

[BagOfWalnuts]

Do you HAVE to use excel for this?

 

[Binky]

This can be easily done with Index/match functions.

Edit - an array formula works fine:

Assume data is in DATA tab, cells A1:F8

Lookup tab is named 108, with 108 in cell A1

Enter in cell A2 as an array formula (shift-enter):

=INDEX(DATA!$A$2:$F$8, SMALL(IF(COUNTIF('108'!$A$1, DATA!$B$2:$B$8), ROW(DATA!$A$2:$F$8)-MIN(ROW(DATA!$A$2:$F$8))+1), ROW(A1)), COLUMN(A1))

Copy that over through column F, and down as far as you want. It will error out after it finds all occurrences of "Code."

 

[beginner99]

This! [runs]

[runs back]OK, maybe you could do something with a pivot table; I'm not sure. [runs again]

Often the stuff people do do solve such problems is actually about 10xtimes more complex than using access.

1) sort by code and do manual copy & paste (with little data this is the fastest solution)
2) VBA -> takes some time to create but in this case simple, fast with a lot of data and reusable

 

[postmark]

You should really look into creating a macro to do this. That way you can create the sheets on the fly based on what is in the code field. But in reality, you should be doing this in a DB program like others have said

 

[postmark]

OK I was bored so I created a macro to do this for you. It will go through your data sheet and look at the code column. It will then check to see if a sheet with that code name already exists, if it does, then it pastes the current row to the bottom of that sheet. If the sheet doesn't exist, it will create it and paste it there.

Let me know if you have any questions.

Sub Macro10()
'
' Macro10 Macro
'

' Change this to whatever your sheet with the data is called
DataSheet = "Sheet1"

' Change this to whatever column your Code column is
CodeColumn = "B"

Dim ThisCode As String

Sheets(DataSheet).Select
' Determine how many rows we have populated
FinalRow = Range("B650000").End(xlUp).Row

' loop through them
For x = 2 To FinalRow
    
    Sheets(DataSheet).Select
    
    ThisCode = Range(CodeColumn & x).Value
    Range("A" & x & ":Z" & x).Select
    Selection.Copy
    
    If FoundWS(ThisCode) Then
        
        ActiveWorkbook.Sheets(ThisCode).Select
        PasteRow = Range("A650000").End(xlUp).Row + 1
        Range("A" & PasteRow).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Else
        Sheets.Add after:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = ThisCode
        ActiveWorkbook.Sheets(ThisCode).Select
        PasteRow = Range("A650000").End(xlUp).Row + 1
        Range("A" & PasteRow).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        
        Sheets(DataSheet).Select
        Range("A1:Z1").Select
        Selection.Copy
        
        Sheets(ThisCode).Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        
        
    End If
Next

End Sub

Function FoundWS(SheetName As String) As Boolean

On Error GoTo NoSheet
ActiveWorkbook.Sheets(SheetName).Select
FoundWS = True
Exit Function

NoSheet:
FoundWS = False

End Function

 

[Homerboy]

Thanks postmark. I will review this and give it a whirl.

For those telling me to dump Excel, as it stands right now, I'm only using it as the reporting tool. Not the DB. The backend is SQL.

Open the Excel file, it pulls the necessary info from SQL into Excel. Excel is then used to further filter those results.

 

[Binky]

Hey now, wheres the love for my (almost) equally effective array formula. My formula has the advantage that it doesn't have to be run!

 

[postmark]

Hey now, wheres the love for my (almost) equally effective array formula. My formula has the advantage that it doesn't have to be run!

But you have to copy and paste and create all the sheets. This handles it all!

 

[Homerboy]

I cheated.
I just created quick simple queries to SQL for each sheet (for now). Takes about 5 secs for everything to pull. I will mess with the VBA junk later. I ran out of patience for today.