Creating a List - Please help!
Posted by Brandon on February 14, 2002 2:12 PM
Hi there.
I have a range from which I want to extract certain data. I'm using VLOOKUP, which is fine for looking for the first instance of the data, and returning the value I want. What I want though is to be able to look up the next instance, to be returned in the next row, and so on.
I'm racking my brains to work this out - can anyone help please?
Posted by Richard S on February 14, 2002 2:21 PM
Autofilter? (nt)
Hi there.
Posted by Brandon on February 14, 2002 2:32 PM
Re: Autofilter? (nt)
I know how to use Autofilter - and what I'm wanting is similar, however I want to have the data on one worksheet and the results on another.
Let me tell you what I'm actually doing. I have a list of open activations orders that have been sent to the telecoms company I work for. These have details such as order number etc. From this data I'm creating lists for our delaers, which show what orders they have. Therefore I want to look up the dealer name in the data, and return the orders that contain that dealer name onto another worksheet.
Posted by Chris D on February 14, 2002 3:11 PM
Re: Autofilter? (nt)
if the info is row-based, you could pivot it
then double clicking on the pivot results will drill down and give you each catagory on its own sheet
I do this for accounts pivotted by tax code, then drill down on any that have a weird-looking result : it gives you a pre-formatted sheet of all the tax codes for that account - sounds just what you're after
however, it's manual, you have to do all the work and it's not auto-refreshing
HTH]
Chris
Posted by Brandon on February 14, 2002 3:16 PM
Re: Autofilter? (nt)
Thanks for that. I have tried pivoting, but there isk too much data for the pivot table to work.
: I know how to use Autofilter - and what I'm wanting is similar, however I want to have the data on one worksheet and the results on another. : Let me tell you what I'm actually doing. I have a list of open activations orders that have been sent to the telecoms company I work for. These have details such as order number etc. From this data I'm creating lists for our delaers, which show what orders they have. Therefore I want to look up the dealer name in the data, and return the orders that contain that dealer name onto another worksheet. :
Posted by Brandon on February 14, 2002 3:16 PM
Re: Autofilter? (nt)
Thanks for that. I have tried pivoting, but there is too much data for the pivot table to work.
: I know how to use Autofilter - and what I'm wanting is similar, however I want to have the data on one worksheet and the results on another. : Let me tell you what I'm actually doing. I have a list of open activations orders that have been sent to the telecoms company I work for. These have details such as order number etc. From this data I'm creating lists for our delaers, which show what orders they have. Therefore I want to look up the dealer name in the data, and return the orders that contain that dealer name onto another worksheet. :
Posted by Richard S on February 14, 2002 5:06 PM
Re:Advanced filter?
What about advanced filter? If you set the criteria as compant equal to the one you want, the extract the fields you want. Only problem is, it only works on the active sheet. If you want the data on another sheet, you have ot copy.
Richard Thanks for that. I have tried pivoting, but there isk too much data for the pivot table to work. : if the info is row-based, you could pivot it : then double clicking on the pivot results will drill down and give you each catagory on its own sheet : I do this for accounts pivotted by tax code, then drill down on any that have a weird-looking result : it gives you a pre-formatted sheet of all the tax codes for that account - sounds just what you're after : however, it's manual, you have to do all the work and it's not auto-refreshing : HTH] : Chris :
Posted by Brandon on February 14, 2002 7:01 PM
Re:Advanced filter?
Thanks for that, unfortunately though it's not really what I'm wanting. I'm trying to automate it for other people to use.
What about advanced filter? If you set the criteria as compant equal to the one you want, the extract the fields you want. Only problem is, it only works on the active sheet. If you want the data on another sheet, you have ot copy. Richard : Thanks for that. I have tried pivoting, but there isk too much data for the pivot table to work. :
Posted by Derek on February 15, 2002 9:52 AM
Re: Wot a challenge but think I've cracked it for you
Hi Brandon
Set this up;
In WorkSheet2
A10:A40 Enter Jones, Bloggs, Smith, Brown (mix them up)
C10:C40 Name in A + sequential number (eg Jones1, Jones2, Bloggs1,Bloggs2)
E10:E40 Number cells 10 - 40
H10:H40 Any other data
In WorkSheet1 (in cell indicated, enter the formula)
B1 =MATCH(1,$B$2:B14,0)-1
A7 =IF(ISERROR(MATCH($C$2,Sheet2!A10:A40,0)+ROW()-ROW($A$2)-$B$1),"",MATCH($C$2,Sheet2!A10:A40,0)+ROW()-ROW($A$2)-$B$1))
B7 =IF(ISERROR(SMALL(A:A,B6)),"",SMALL(A:A,B6))
C7 =IF(ISERROR(INDIRECT(C$3&$B7+$D$2-2)),"",INDIRECT(C$3&$B7+$D$2-2))
Now change the formula in A7 so that your sheet & range replaces Sheet2!A10:A40 (2 places)
Select C7 and scroll formula right to D7 and E7
Select A7:E7 and scroll formulas way down
Very Important: Colour A1:A6 and B2:B5 RED (these cells must always be totally blank)
Very Important: Type 1 in B6
Border C2 and colour it green (in C1 above it type "Name")
Border D2 and colour it yellow (in D1 above type "Data top row")
Very Important: In yellow cell D2 type the top row of your data range ( eg for Sheet2!A10:A40 the top row is 10)
If you change your data range you must change the number in yellow cell D2
In C3 type Sheet2!C
In D3 type Sheet2!E
In E3 type Sheet2!H
Hide columns A:B if you wish (to protect formulas)
Now in the green cell C2 type one of the names (eg Brown)
Columns C,D.E, now display your data for Brown
(for data in different columns of Sheet2, change the column reference
in row 3 of Sheet1 - eg Sheet2!C becomes Sheet2!D etc etc
In Sheet1, if you wish, you can select Row 1 to insert rows above it or
you can select Row 5 to insert rows there. Selecting other rows for insert/delete is not recommended
Hope this helps you 'cos it's taken me from 3pm to O130am to crack this!
Derek
Hi there.
Posted by Derek on February 15, 2002 9:58 AM
Re:Advanced filter? See posting at top please (NT)
: What about advanced filter? If you set the criteria as compant equal to the one you want, the extract the fields you want. Only problem is, it only works on the active sheet. If you want the data on another sheet, you have ot copy. : Richard
Posted by Brandon on February 17, 2002 2:08 PM
Re: Wot a challenge but think I've cracked it for you
Wow! I can't believe that you spent that long on it for me :-) You must love a challenge!
Anyway - I have done everything in what you have said - and Sheet1 C7:E7 do display the data. However none of the rows below that display the next instance of the name and associated data.
Something that I wasn't quite clear about was Sheet2 C10:C40 - Was I meant to match A10:A40, ie, the first instance of Jones in A10:A40 would be Jones1, the second instance Jones2 etc?
Have you got a copy of the workbook you created that you could perhaps send me, so i can check I have understood your instructions?
I really appreciate your help on this.
Cheers,
Brandon
Hi Brandon Set this up; In WorkSheet2 A10:A40 Enter Jones, Bloggs, Smith, Brown (mix them up) C10:C40 Name in A + sequential number (eg Jones1, Jones2, Bloggs1,Bloggs2) E10:E40 Number cells 10 - 40 H10:H40 Any other data In WorkSheet1 (in cell indicated, enter the formula) B1 =MATCH(1,$B$2:B14,0)-1 A7 =IF(ISERROR(MATCH($C$2,Sheet2!A10:A40,0)+ROW()-ROW($A$2)-$B$1),"",MATCH($C$2,Sheet2!A10:A40,0)+ROW()-ROW($A$2)-$B$1)) B7 =IF(ISERROR(SMALL(A:A,B6)),"",SMALL(A:A,B6)) C7 =IF(ISERROR(INDIRECT(C$3&$B7+$D$2-2)),"",INDIRECT(C$3&$B7+$D$2-2)) Now change the formula in A7 so that your sheet & range replaces Sheet2!A10:A40 (2 places) Select C7 and scroll formula right to D7 and E7 Select A7:E7 and scroll formulas way down Very Important: Colour A1:A6 and B2:B5 RED (these cells must always be totally blank) Very Important: Type 1 in B6 Border C2 and colour it green (in C1 above it type "Name") Border D2 and colour it yellow (in D1 above type "Data top row") Very Important: In yellow cell D2 type the top row of your data range ( eg for Sheet2!A10:A40 the top row is 10) If you change your data range you must change the number in yellow cell D2 In C3 type Sheet2!C In D3 type Sheet2!E In E3 type Sheet2!H Hide columns A:B if you wish (to protect formulas) Now in the green cell C2 type one of the names (eg Brown) Columns C,D.E, now display your data for Brown (for data in different columns of Sheet2, change the column reference in row 3 of Sheet1 - eg Sheet2!C becomes Sheet2!D etc etc In Sheet1, if you wish, you can select Row 1 to insert rows above it or you can select Row 5 to insert rows there. Selecting other rows for insert/delete is not recommended Hope this helps you 'cos it's taken me from 3pm to O130am to crack this! Derek
: Hi there.
Posted by Brandon on February 17, 2002 2:19 PM
Re: Wot a challenge but think I've cracked it for you
Hi Derek,
Ignore that last message about the next rows not returning data, I hadn't copied the formulas down.
Cheers,
Brandon
Wow! I can't believe that you spent that long on it for me :-) You must love a challenge! Anyway - I have done everything in what you have said - and Sheet1 C7:E7 do display the data. However none of the rows below that display the next instance of the name and associated data. Something that I wasn't quite clear about was Sheet2 C10:C40 - Was I meant to match A10:A40, ie, the first instance of Jones in A10:A40 would be Jones1, the second instance Jones2 etc? Have you got a copy of the workbook you created that you could perhaps send me, so i can check I have understood your instructions? I really appreciate your help on this. Cheers, Brandon : Hi Brandon : Set this up; : In WorkSheet2 : A10:A40 Enter Jones, Bloggs, Smith, Brown (mix them up) : C10:C40 Name in A + sequential number (eg Jones1, Jones2, Bloggs1,Bloggs2) : E10:E40 Number cells 10 - 40 : H10:H40 Any other data : : In WorkSheet1 (in cell indicated, enter the formula) : B1 =MATCH(1,$B$2:B14,0)-1 : A7 =IF(ISERROR(MATCH($C$2,Sheet2!A10:A40,0)+ROW()-ROW($A$2)-$B$1),"",MATCH($C$2,Sheet2!A10:A40,0)+ROW()-ROW($A$2)-$B$1)) : B7 =IF(ISERROR(SMALL(A:A,B6)),"",SMALL(A:A,B6)) : C7 =IF(ISERROR(INDIRECT(C$3&$B7+$D$2-2)),"",INDIRECT(C$3&$B7+$D$2-2)) : Now change the formula in A7 so that your sheet & range replaces Sheet2!A10:A40 (2 places) : Select C7 and scroll formula right to D7 and E7 : Select A7:E7 and scroll formulas way down : Very Important: Colour A1:A6 and B2:B5 RED (these cells must always be totally blank) : Very Important: Type 1 in B6 : Border C2 and colour it green (in C1 above it type "Name") : Border D2 and colour it yellow (in D1 above type "Data top row") : Very Important: In yellow cell D2 type the top row of your data range ( eg for Sheet2!A10:A40 the top row is 10) : If you change your data range you must change the number in yellow cell D2 : In C3 type Sheet2!C : In D3 type Sheet2!E : In E3 type Sheet2!H : Hide columns A:B if you wish (to protect formulas) : Now in the green cell C2 type one of the names (eg Brown) : Columns C,D.E, now display your data for Brown : (for data in different columns of Sheet2, change the column reference : in row 3 of Sheet1 - eg Sheet2!C becomes Sheet2!D etc etc : In Sheet1, if you wish, you can select Row 1 to insert rows above it or : you can select Row 5 to insert rows there. Selecting other rows for insert/delete is not recommended : Hope this helps you 'cos it's taken me from 3pm to O130am to crack this! : Derek :
Posted by Brandon on February 17, 2002 2:19 PM
Re: Wot a challenge but think I've cracked it for you
Hi Derek,
Ignore that last message about the next rows not returning data, I hadn't copied the formulas down.
Cheers,
Brandon
Wow! I can't believe that you spent that long on it for me :-) You must love a challenge! Anyway - I have done everything in what you have said - and Sheet1 C7:E7 do display the data. However none of the rows below that display the next instance of the name and associated data. Something that I wasn't quite clear about was Sheet2 C10:C40 - Was I meant to match A10:A40, ie, the first instance of Jones in A10:A40 would be Jones1, the second instance Jones2 etc? Have you got a copy of the workbook you created that you could perhaps send me, so i can check I have understood your instructions? I really appreciate your help on this. Cheers, Brandon : Hi Brandon : Set this up; : In WorkSheet2 : A10:A40 Enter Jones, Bloggs, Smith, Brown (mix them up) : C10:C40 Name in A + sequential number (eg Jones1, Jones2, Bloggs1,Bloggs2) : E10:E40 Number cells 10 - 40 : H10:H40 Any other data : : In WorkSheet1 (in cell indicated, enter the formula) : B1 =MATCH(1,$B$2:B14,0)-1 : A7 =IF(ISERROR(MATCH($C$2,Sheet2!A10:A40,0)+ROW()-ROW($A$2)-$B$1),"",MATCH($C$2,Sheet2!A10:A40,0)+ROW()-ROW($A$2)-$B$1)) : B7 =IF(ISERROR(SMALL(A:A,B6)),"",SMALL(A:A,B6)) : C7 =IF(ISERROR(INDIRECT(C$3&$B7+$D$2-2)),"",INDIRECT(C$3&$B7+$D$2-2)) : Now change the formula in A7 so that your sheet & range replaces Sheet2!A10:A40 (2 places) : Select C7 and scroll formula right to D7 and E7 : Select A7:E7 and scroll formulas way down : Very Important: Colour A1:A6 and B2:B5 RED (these cells must always be totally blank) : Very Important: Type 1 in B6 : Border C2 and colour it green (in C1 above it type "Name") : Border D2 and colour it yellow (in D1 above type "Data top row") : Very Important: In yellow cell D2 type the top row of your data range ( eg for Sheet2!A10:A40 the top row is 10) : If you change your data range you must change the number in yellow cell D2 : In C3 type Sheet2!C : In D3 type Sheet2!E : In E3 type Sheet2!H : Hide columns A:B if you wish (to protect formulas) : Now in the green cell C2 type one of the names (eg Brown) : Columns C,D.E, now display your data for Brown : (for data in different columns of Sheet2, change the column reference : in row 3 of Sheet1 - eg Sheet2!C becomes Sheet2!D etc etc : In Sheet1, if you wish, you can select Row 1 to insert rows above it or : you can select Row 5 to insert rows there. Selecting other rows for insert/delete is not recommended : Hope this helps you 'cos it's taken me from 3pm to O130am to crack this! : Derek :
Posted by Brandon on February 18, 2002 6:39 PM
Re:Advanced filter? See posting at top please (NT)
Worked fantastically Derek, thanks very much for your help. I did try sending you an email thanking you, but your email address is not a real one, though I can understand that.
So - thanks again!!!
Cheers,
Brandon
