Data sorting

[redsalt]

Let me simplify my question:
1) On Sheet 1 I have 3 client names with data corresponding with them. There are multiple entries on different dates for these clients
2) On next sheets 2-4 i want to sort the data of the three clients i.e. John, Joe, and Jack.
3) If for example on sheet 3 row 1, i am looking for Client John, on Sheet 1 Row 1 it doesn't have John, I want it to search for the next row John's name was enetered on the first Sheet
Thanks for any help
This message was edited by redsalt on 2002-11-06 08:58

 

[DonkeyOte]

would recommend a For Each Next Loop..

e.g. a test where DATA contains names:

DATA = Range("A1:A5")
TOT = 0
CRIT = "Luke"

For Each LDATA in DATA

If LDATA = CRIT THEN TOT = TOT + 1

Next LDATA

Range("B1") = TOT

You can create a double loop so that it can loop through a list of criteria (i.e. different names) and then do the above loop for each criteria, when done it loops to the next criteria and runs again.

Is this along the right lines?

(Amending this to check data in another workbook is straightforward)

 

[redsalt]

What would the formula look like then?

 

[DonkeyOte]

sorry this is VB...

As a formula you could possible use MATCH

eg...

=INDIRECT("[fpdata2002.XLS]ORDERS!B"&MATCH("Jacobson_Jim",[fpdata2002.XLS]ORDERS!L1254:1261,0)+1253)




_________________
LASW10
This message was edited by lasw10 on 2002-11-06 08:51

 

[redsalt]

I tried the formula but the reslut had an error

 

[DonkeyOte]

can you email me (else we'll have masses of posts)...was the error #n/a

 

[redsalt]

yes the error was #n/a

 

[DonkeyOte]

First off, ignore the INDIRECT bit and try:

=MATCH("Jacobson_Jim",[fpdata2002.XLS]ORDERS!L1254:1261,0)+1253

check to make sure there's a Jacobson_Jim entry in the range you're specifying - if there is make sure they are EXACTLY the same (i.e no spaces after Jim etc... - do LEN(celref) to get the length of the cell - this should be identical.

If there's definitely an exact match and you're still getting NA's let us know...

Hopefully Aladin will have read this...then trust me, your problems will be over!

Luke

 

[redsalt]

=MATCH("Jacobson_Jim",ORDERS!L1254:1261,0)+1253

Ok it gave me an error when i entered it aswell, If the name does equal Jacobson_Jim then i want it to input that data in the cell.