conditional count with listing

[Eddy Stan]

Hi

I have data in sheet2 (data row7:row4000/ Title row1:6) and i want result in
sheet1
data is asorted type (consolidated from daily data)

sheet2 has many columns, where column 3 and 4 are to be checked
column 3 has branchname and 4 has brokercode

if brokercode is found in one branch only, skip that broker (no need to list)
if brokercode is found in more than one location, then list that branch
name, broker code and count transactions with that broker for each location.
result to show as below in sheet1:
Broker branch count
aaaa branch1 50
branch2 20
bbbb branch1 15
branch2 7
branch3 17

so if broker at single location is 800 then balance 3200 transactions must
have a break up like above

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Eddy Stan]

Hi Don,
I have just attached file by mail ([email protected])
for convenience i have put only 2 columns
branch name & broker code
You can put broker code, branch name and count()
if broker is found in one branch, ignore him
if broker is fuond in more than one branch, then list broker, branch & count
for fisrt branch, second branch, third branch....

best regards,
Eddy stan

 

[Don Guillett]

I did NOT see any instance in your example where the broker was associated
with more than one branch. You must tell us how you want this done and
provide before/after examples using the actual data.

 

[Eddy Stan]

Hi Don,
the data i sent is actual but i copied column 3 branch, column 18 broker
number,
the data will be not sorted one, as it is appended as & when data is received,
so you need to sort ( i prefer vsort(), but ofcourse you need to pass start
row3: endrow number as parameter to vsort() function)
May be you can populate data for other columns between 1 to 18 & put my data
at column 3 & 18, for testing.
the following brokers is repeating in morethan one branch, in the data i sent
Broker No Branch Name Fq Broker at
NID06090111 Alwar 1 2
NID06090111 Indore 2 2
STR04090001 Salem 14 2
STR04090001 Trichy 95 2
STR04090053 Salem 2 2
STR04090053 Trichy 1 2

the above result i got by using excel /advanced filter for unique values
Fq by sumproduct(), broker at by countif(), this i can do all the time, but
i cannot ask someone to do this, so i want to give a macro to run to get the
above result in new sheet.

the original data set has 52 columns (the whole transportation business
transactions), the branch name is at 2nd column and broker no is at 18th
column but i am taking this to new sheet by vsort(), so that the broker is in
column 2 and branch name is at 3rd colum as you see, in the file i sent.
The reason to extract the statistics of broker serving at more than one
place is to offer better terms, based on various other prospects.

My plan is:
(1) take the whole data sheet as one set and by offset write column
18(broker) & 3(branch) in the newsheet. then sort the same by broker (by
vsort() ofcourse you have to find last row & give to vsort()) - now you have
sorted set
(2) find uniquevalues from sorted set - write next to sorted set
(3) compare each broker number in unique set with sorted set
and write in new sheet(Prospective broker report), only if broker has served
more than one location
suppose, if broker has served 3 locations then we have to give all the three
location names & counts. so all the time when new broker starts we have to
keep count
when we find him serving at 2nd branch, write 1st branch name & count, and
2nd branch name (keep counting) till the branch name change while broker
number is matching, then write. suppose the broker is changing then write 2nd
branch count.

I know foxpro (those days are gone) now VB rules, so really find hard to fix
this. pls help.