Count

[AS]

Hi,

I am trying to copy Heading and rows to new worksheet for each employee id
in new worksheet with worksheet name to be apper with employeeid.

Example:

Employee ID Employee Name Amount
9100 John 15.00
9100 John 25.99
9200 Marc 14.99

Needs to create 2 worksheet based on above example, with information to be
copied to new wokrsheet for that employee only. Also in new worksheet need to
add column to count the number of record.

Employee ID Employee Name Amount Count
9100 John 15.00 2
9100 John 25.99 2

sheet(9100)

Employee ID Employee Name Amount Count
9200 Marc 14.99 1

sheet(9200)

Yours help will be appreciated.

Thanks

 

[Max]

One easy set-up which automates it using non-array formulas ..

Illustrated in this implemented sample:
http://freefilehosting.net/download/424dh
AutoCopy Lines by Emp IDs to Resp Shts.xls

In sheet: WS1 (the "master")
Assume data in cols A to C, data in row2 down,
with the key col = col A (as per reqt)

List the key col values (col A's unique values) in K1 across,
in **text** format (Just preformat K1 across as TEXT):
9100, 9200, etc (the text numbers can be in any order)

Put in K2: =IF($A2="","",IF($A2=K$1+0,ROW(),""))
Copy K2 across & fill down to cover
the max expected extent of source data in the key col A

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name
we can use to refer to the sheetname in formulas
It will auto-extract the sheetname implicitly
Technique came from a post by Harlan

In a new sheet named: 9100
With the same col headers pasted into A1:C1

Put in A2:
=IF(ROWS($1:1)>COUNT(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($1:1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),0)))

Copy A2 across to C2, fill down to say, C10
(copy down by the smallest possible range sufficient
to cover the max expected extent for any key col value.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)

In D1: Count
In D2: =IF(A2="","",COUNT(A:A))
Copy down

Cols A to D will return only the lines
for the key col value: 9100 from "WS1",
with all lines neatly packed at the top

Dress this sheet up nicely to taste, then just make copies of it, rename as
the other key col value: 9200, etc to get corresponding returns. Adapt to
suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---