| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to auto extract item# upon entering a date
i have the following data:-
Items # Date ABC 2/4/09 DEF 4/5/09 AEG 2/4/09 I want to auto extract items# when you enter a date, but i do have a duplicate date how do i auto extract when there is a duplicate date:- Date Items 2/4/09 ABC 2/4/09 AEG thanks for your help meeru |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
Easiest is to apply autofilter on the dates col in the source table & filter
out by the desired date. If you wish to extract it directly in another sheet, try this simple formulas play: Asume source table as posted in Sheet1, cols A and B, data from row2 down In another sheet, Assume the date of interest will be input in A2 In B2: =IF(A$2="","",IF(Sheet1!B2=A$2,ROW(),"")) In C2: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(Sheet1!A:A,SMAL L(B:B,ROWS($1:1)))) Copy B2:C2 down to cover the max expected extent of source data. Hide/minimize col B. Col C will return the required items. Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Meeru" wrote: i have the following data:- Items # Date ABC 2/4/09 DEF 4/5/09 AEG 2/4/09 I want to auto extract items# when you enter a date, but i do have a duplicate date how do i auto extract when there is a duplicate date:- Date Items 2/4/09 ABC 2/4/09 AEG thanks for your help meeru |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
Try the below test
Col A Col B Col C Col D Items Date QDate Items ABC 2/4/2009 2/4/2009 = DEF 4/5/2009 2/4/2009 = AEG 2/4/2009 AEH 2/4/2009 --QDate represents Query Date. --In cell D2; enter the below formula; Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100=C2,ROW($B$ 1:$B$100)),ROW(A1))) If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: i have the following data:- Items # Date ABC 2/4/09 DEF 4/5/09 AEG 2/4/09 I want to auto extract items# when you enter a date, but i do have a duplicate date how do i auto extract when there is a duplicate date:- Date Items 2/4/09 ABC 2/4/09 AEG thanks for your help meeru |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
thanks Max. i tried and i got the following:-
Date Items 2/4/2009 2 ABC AEG 4 AEH 5 I just put one date in A2 and i got the results in b2:b4, even though i did not enter any dates from A3 onwards. "Max" wrote: Easiest is to apply autofilter on the dates col in the source table & filter out by the desired date. If you wish to extract it directly in another sheet, try this simple formulas play: Asume source table as posted in Sheet1, cols A and B, data from row2 down In another sheet, Assume the date of interest will be input in A2 In B2: =IF(A$2="","",IF(Sheet1!B2=A$2,ROW(),"")) In C2: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(Sheet1!A:A,SMAL L(B:B,ROWS($1:1)))) Copy B2:C2 down to cover the max expected extent of source data. Hide/minimize col B. Col C will return the required items. Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Meeru" wrote: i have the following data:- Items # Date ABC 2/4/09 DEF 4/5/09 AEG 2/4/09 I want to auto extract items# when you enter a date, but i do have a duplicate date how do i auto extract when there is a duplicate date:- Date Items 2/4/09 ABC 2/4/09 AEG thanks for your help meeru |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
jacob, i tried and it gave the below result, i entered the formula as you
have mentioned, i enter dates in c2 and get the result from d2:d5, is there something wrong in the formula. Date Items 2/4/2009 ABC 4/5/2009 ABC ABC ABC "Jacob Skaria" wrote: Try the below test Col A Col B Col C Col D Items Date QDate Items ABC 2/4/2009 2/4/2009 = DEF 4/5/2009 2/4/2009 = AEG 2/4/2009 AEH 2/4/2009 --QDate represents Query Date. --In cell D2; enter the below formula; Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100=C2,ROW($B$ 1:$B$100)),ROW(A1))) If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: i have the following data:- Items # Date ABC 2/4/09 DEF 4/5/09 AEG 2/4/09 I want to auto extract items# when you enter a date, but i do have a duplicate date how do i auto extract when there is a duplicate date:- Date Items 2/4/09 ABC 2/4/09 AEG thanks for your help meeru |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
I just put one date in A2 and i got the results in b2:b4, even though i did
not enter any dates from A3 onwards. Yes, of course. The results, btw are in col C, not col B. Col B is a helper criteria col which can be hidden away (as mentioned in my response). Isn't that how you wanted it extracted? The source item/multiple items corresponding to that date input in A2 will be listed in col C, neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Meeru" wrote: thanks Max. i tried and i got the following:- Date Items 2/4/2009 2 ABC AEG 4 AEH 5 I just put one date in A2 and i got the results in b2:b4, even though i did not enter any dates from A3 onwards. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
sorry, mac, the results are indeed shown in col C and not in col B, it was my
typing error when i typed that results are shown in col B, i meant col C and not col B. In column A2, i only typed 2/4/2009 and i get the following :- In column B1 to B4 i get 2,space,4,5 In column C1 to C4 i get ABC,AEG,AEH thanks for your kind clarification. "Max" wrote: I just put one date in A2 and i got the results in b2:b4, even though i did not enter any dates from A3 onwards. Yes, of course. The results, btw are in col C, not col B. Col B is a helper criteria col which can be hidden away (as mentioned in my response). Isn't that how you wanted it extracted? The source item/multiple items corresponding to that date input in A2 will be listed in col C, neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Meeru" wrote: thanks Max. i tried and i got the following:- Date Items 2/4/2009 2 ABC AEG 4 AEH 5 I just put one date in A2 and i got the results in b2:b4, even though i did not enter any dates from A3 onwards. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
--Arrange the data layout as below
Col C is Query Date Col D,E,F etc; will display multiples items (if any) --The formula in cell D2 is below again array entered (CTRL+SHIFT+ENTER) =IF(COUNTIF($B$1:$B$100,$C2)=COLUMN(A1),INDEX($A$ 1:$A$100,SMALL(IF($B$1:$B$100=$C2,ROW($B$1:$B$100) ),COLUMN(A1))),"") Copy to E2,F2 ....and copy down as required. Try and feedback Col A Col B Col C Col D Col E Col F Items Date Q Date Item 1 Item 2 Item 3 ABC 2/4/2009 2/4/2009 ABC AEG AEH DEF 4/5/2009 4/5/2009 DEF AEG 2/4/2009 AEH 2/4/2009 If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: jacob, i tried and it gave the below result, i entered the formula as you have mentioned, i enter dates in c2 and get the result from d2:d5, is there something wrong in the formula. Date Items 2/4/2009 ABC 4/5/2009 ABC ABC ABC "Jacob Skaria" wrote: Try the below test Col A Col B Col C Col D Items Date QDate Items ABC 2/4/2009 2/4/2009 = DEF 4/5/2009 2/4/2009 = AEG 2/4/2009 AEH 2/4/2009 --QDate represents Query Date. --In cell D2; enter the below formula; Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100=C2,ROW($B$ 1:$B$100)),ROW(A1))) If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: i have the following data:- Items # Date ABC 2/4/09 DEF 4/5/09 AEG 2/4/09 I want to auto extract items# when you enter a date, but i do have a duplicate date how do i auto extract when there is a duplicate date:- Date Items 2/4/09 ABC 2/4/09 AEG thanks for your help meeru |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
Hi Meeru
If you want your data to be in the same column and not to spread across columns; use the below formula In D2 enter the array formula =IF(COUNTIF($B$1:$B$100,C2)=COUNTIF(C$1:C2,C2),IN DEX($A$1:$A$100,SMALL(IF($B$1:$B$100=$C2,ROW($B$1: $B$100)),COUNTIF(C$1:C2,C2))),"") Result as below Col A Col B Col C Col D Items Date Date Item ABC 2/4/2009 2/4/2009 ABC DEF 4/5/2009 4/5/2009 DEF AEG 2/4/2009 2/4/2009 AEG AEH 2/4/2009 If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: jacob, i tried and it gave the below result, i entered the formula as you have mentioned, i enter dates in c2 and get the result from d2:d5, is there something wrong in the formula. Date Items 2/4/2009 ABC 4/5/2009 ABC ABC ABC "Jacob Skaria" wrote: Try the below test Col A Col B Col C Col D Items Date QDate Items ABC 2/4/2009 2/4/2009 = DEF 4/5/2009 2/4/2009 = AEG 2/4/2009 AEH 2/4/2009 --QDate represents Query Date. --In cell D2; enter the below formula; Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100=C2,ROW($B$ 1:$B$100)),ROW(A1))) If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: i have the following data:- Items # Date ABC 2/4/09 DEF 4/5/09 AEG 2/4/09 I want to auto extract items# when you enter a date, but i do have a duplicate date how do i auto extract when there is a duplicate date:- Date Items 2/4/09 ABC 2/4/09 AEG thanks for your help meeru |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to auto extract item# upon entering a date
It worked. thank you very much Jacob.
"Jacob Skaria" wrote: Hi Meeru If you want your data to be in the same column and not to spread across columns; use the below formula In D2 enter the array formula =IF(COUNTIF($B$1:$B$100,C2)=COUNTIF(C$1:C2,C2),IN DEX($A$1:$A$100,SMALL(IF($B$1:$B$100=$C2,ROW($B$1: $B$100)),COUNTIF(C$1:C2,C2))),"") Result as below Col A Col B Col C Col D Items Date Date Item ABC 2/4/2009 2/4/2009 ABC DEF 4/5/2009 4/5/2009 DEF AEG 2/4/2009 2/4/2009 AEG AEH 2/4/2009 If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: jacob, i tried and it gave the below result, i entered the formula as you have mentioned, i enter dates in c2 and get the result from d2:d5, is there something wrong in the formula. Date Items 2/4/2009 ABC 4/5/2009 ABC ABC ABC "Jacob Skaria" wrote: Try the below test Col A Col B Col C Col D Items Date QDate Items ABC 2/4/2009 2/4/2009 = DEF 4/5/2009 2/4/2009 = AEG 2/4/2009 AEH 2/4/2009 --QDate represents Query Date. --In cell D2; enter the below formula; Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100=C2,ROW($B$ 1:$B$100)),ROW(A1))) If this post helps click Yes --------------- Jacob Skaria "Meeru" wrote: i have the following data:- Items # Date ABC 2/4/09 DEF 4/5/09 AEG 2/4/09 I want to auto extract items# when you enter a date, but i do have a duplicate date how do i auto extract when there is a duplicate date:- Date Items 2/4/09 ABC 2/4/09 AEG thanks for your help meeru |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto insert row while entering raw data | Excel Discussion (Misc queries) | |||
| extract 1 of each item | Excel Discussion (Misc queries) | |||
| Extract each item in the list | Excel Worksheet Functions | |||
| Extract each item in the list | Excel Worksheet Functions | |||
| auto dating after entering the first date | Excel Worksheet Functions | |||
Linear Mode
