| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula locates common data in a cell and drops them in other shee
I have a spread sheet with a range of data from columns A - I.
The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other shee
Hi,
You can auto filter the sheet one and use custom filter and for HYPER make your condition like: Equals: *HYPER or for AA Equals: *AA etc... when you filtered it you can copy it to another sheet too Thanks, -- Farhad Hodjat "Barry Walker" wrote: I have a spread sheet with a range of data from columns A - I. The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other
Hi Farhad,
I have done this already so far and that works fine, but I want something faster than that. As in it will just find them and drop them. Maybe a macro but preferably a formula. regards Blade "Farhad" wrote: Hi, You can auto filter the sheet one and use custom filter and for HYPER make your condition like: Equals: *HYPER or for AA Equals: *AA etc... when you filtered it you can copy it to another sheet too Thanks, -- Farhad Hodjat "Barry Walker" wrote: I have a spread sheet with a range of data from columns A - I. The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other
I think the faster way could be possible by writing code.
Thanks, -- Farhad Hodjat "Barry Walker" wrote: Hi Farhad, I have done this already so far and that works fine, but I want something faster than that. As in it will just find them and drop them. Maybe a macro but preferably a formula. regards Blade "Farhad" wrote: Hi, You can auto filter the sheet one and use custom filter and for HYPER make your condition like: Equals: *HYPER or for AA Equals: *AA etc... when you filtered it you can copy it to another sheet too Thanks, -- Farhad Hodjat "Barry Walker" wrote: I have a spread sheet with a range of data from columns A - I. The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other shee
In columns JKL of sheet1 write:
cell j1:=IF(RIGHT(G1,5)="hyper",1,0) cell k1:=IF(RIGHT(G1,2)="AA",1,0) cell l1:=IF(RIGHT(G1,2)="PR",1,0) cell j2:=IF(RIGHT(G2,5)="hyper",1,0)+J1 cell k2:=IF(RIGHT(G2,5)="hyper",1,0)+K1 cell L2:=IF(RIGHT(G2,5)="hyper",1,0)+L1 and drop formulas down. In sheet2, fill cloumn J with numbers 1, 2... to as mant records you have (let's say 500). Then, in sheet2, cell A1: =INDEX(Sheet1!A$1:A$500,MATCH(Sheet2!J1,Sheet1!$j$ 1:$j$500,0)) and drop the formula down and right to column G. And same for sheets 3 and 4 You'll get #NA errors once you've recovered all records you need. You can either delete those lines, or work around it by using =IF(ISNA(formula),"",formula) "Barry Walker" wrote: I have a spread sheet with a range of data from columns A - I. The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other
Yess that is what I was hoping you or someone may be able to help me with. Do
you no anything like this? or anyone else. Your help is very much appreciated Regards Blade "Farhad" wrote: I think the faster way could be possible by writing code. Thanks, -- Farhad Hodjat "Barry Walker" wrote: Hi Farhad, I have done this already so far and that works fine, but I want something faster than that. As in it will just find them and drop them. Maybe a macro but preferably a formula. regards Blade "Farhad" wrote: Hi, You can auto filter the sheet one and use custom filter and for HYPER make your condition like: Equals: *HYPER or for AA Equals: *AA etc... when you filtered it you can copy it to another sheet too Thanks, -- Farhad Hodjat "Barry Walker" wrote: I have a spread sheet with a range of data from columns A - I. The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other
OK, I messed up one of the relative references. The formula in cell a1,
sheet2 is: =INDEX(Sheet1!A$1:A$500,MATCH(Sheet2!$J1,Sheet1!$J $1:$J$500,0)) PD: Can I edit my posts? "Idoia" wrote: In columns JKL of sheet1 write: cell j1:=IF(RIGHT(G1,5)="hyper",1,0) cell k1:=IF(RIGHT(G1,2)="AA",1,0) cell l1:=IF(RIGHT(G1,2)="PR",1,0) cell j2:=IF(RIGHT(G2,5)="hyper",1,0)+J1 cell k2:=IF(RIGHT(G2,5)="hyper",1,0)+K1 cell L2:=IF(RIGHT(G2,5)="hyper",1,0)+L1 and drop formulas down. In sheet2, fill cloumn J with numbers 1, 2... to as mant records you have (let's say 500). Then, in sheet2, cell A1: =INDEX(Sheet1!A$1:A$500,MATCH(Sheet2!J1,Sheet1!$j$ 1:$j$500,0)) and drop the formula down and right to column G. And same for sheets 3 and 4 You'll get #NA errors once you've recovered all records you need. You can either delete those lines, or work around it by using =IF(ISNA(formula),"",formula) "Barry Walker" wrote: I have a spread sheet with a range of data from columns A - I. The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other
Hi there,
It seems I am very close to what I want to achieve. The formula picks up only column A of my spread sheet into sheet 2 and drops it in with all the rest being NA's. Im not sure what to do. "Idoia" wrote: In columns JKL of sheet1 write: cell j1:=IF(RIGHT(G1,5)="hyper",1,0) cell k1:=IF(RIGHT(G1,2)="AA",1,0) cell l1:=IF(RIGHT(G1,2)="PR",1,0) cell j2:=IF(RIGHT(G2,5)="hyper",1,0)+J1 cell k2:=IF(RIGHT(G2,5)="hyper",1,0)+K1 cell L2:=IF(RIGHT(G2,5)="hyper",1,0)+L1 and drop formulas down. In sheet2, fill cloumn J with numbers 1, 2... to as mant records you have (let's say 500). Then, in sheet2, cell A1: =INDEX(Sheet1!A$1:A$500,MATCH(Sheet2!J1,Sheet1!$j$ 1:$j$500,0)) and drop the formula down and right to column G. And same for sheets 3 and 4 You'll get #NA errors once you've recovered all records you need. You can either delete those lines, or work around it by using =IF(ISNA(formula),"",formula) "Barry Walker" wrote: I have a spread sheet with a range of data from columns A - I. The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other
Sorry, see my edit, I messed up one reference...
The formula in cell a1, sheet2 is: =INDEX(Sheet1!A$1:A$500,MATCH(Sheet2!$J1,Sheet1!$J $1:$J$500,0)) Also, for cells k2 and l2 in sheet1: cell k2:=IF(RIGHT(G2,2)="AA",1,0)+K1 cell L2:=IF(RIGHT(G2,2)="PR",1,0)+L1 I guess attention to detail is not my strength!! "Barry Walker" wrote: Hi there, It seems I am very close to what I want to achieve. The formula picks up only column A of my spread sheet into sheet 2 and drops it in with all the rest being NA's. Im not sure what to do. "Idoia" wrote: In columns JKL of sheet1 write: cell j1:=IF(RIGHT(G1,5)="hyper",1,0) cell k1:=IF(RIGHT(G1,2)="AA",1,0) cell l1:=IF(RIGHT(G1,2)="PR",1,0) cell j2:=IF(RIGHT(G2,5)="hyper",1,0)+J1 cell k2:=IF(RIGHT(G2,5)="hyper",1,0)+K1 cell L2:=IF(RIGHT(G2,5)="hyper",1,0)+L1 and drop formulas down. In sheet2, fill cloumn J with numbers 1, 2... to as mant records you have (let's say 500). Then, in sheet2, cell A1: =INDEX(Sheet1!A$1:A$500,MATCH(Sheet2!J1,Sheet1!$j$ 1:$j$500,0)) and drop the formula down and right to column G. And same for sheets 3 and 4 You'll get #NA errors once you've recovered all records you need. You can either delete those lines, or work around it by using =IF(ISNA(formula),"",formula) "Barry Walker" wrote: I have a spread sheet with a range of data from columns A - I. The number of rows always varies. In column G there is a number or reference followed by a a group of letters. examples below, 123456HYPER ABCD01PC01AA ABCD01PC01PR I need a formula that locates the 'HYPER' of all cells of column G that contain HYPER and drop it with the corresponding contents of cells A - I into a seperate sheet2. the other formula of sheet3 would pick out the 'AA' in column G and drop it and the corresponding details into sheet3 The formula for sheet 4 would pick out the 'PR' in column G and drop it and the corresponding details into sheet 4. In this case then the data would essentially be sorted from sheet1 and dropped into sheets 2 3 and 4. is this possible? any help would be much appreicated! Regards Barry |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula locates common data in a cell and drops them in other
One approach using non-array formulas ..
Assume source data in Sheet1, cols A to G, from row1 down. The key col is col G as described. In Sheet2, Put in A1: =IF(ISNUMBER(SEARCH("HYPER",Sheet1!G1)),ROW(),"") Put in B1: =IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($ A:$A,ROW()))) Copy B1 across to H1. Select A1:H1, fill down to cover the max expected extent of data in Sheet1's col G. Cols B to H will return the required results, ie the lines for "HYPER" from Sheet1's cols A to G, all neatly bunched at the top. Now, to propagate .. just make a copy of Sheet2. In the copy, change the formula in A1 to: =IF(ISNUMBER(SEARCH("AA",Sheet1!G1)),ROW(),"") Copy A1 down. No change needed to formulas in cols B to H. Cols B to H will now return the lines for "AA" from Sheet1's cols A to G. Just repeat the sheet copy n change for the formulas in col A for the rest of the required captures: "PR", etc. Note that SEARCH is not case sensitive. If you need it case sensitive, replace SEARCH with FIND in the formulas for col A. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to pick out common parts of a cell and drop them in new sh | Excel Discussion (Misc queries) | |||
| How can I insert the tab name into a cell in a different shee... | Excel Discussion (Misc queries) | |||
| Combining rows of data that have one cell in common | Excel Discussion (Misc queries) | |||
| Hyperlink Locates file regardless of it's location | Excel Discussion (Misc queries) | |||
| Copy Formula But Keep 1 Common Cell | Excel Discussion (Misc queries) | |||
Linear Mode
