Extracting data in a given Tabular Format to a new sheet (Time,Date,Text,etc...)
- Thread starter NJ786
- Start date
Faseeh
Excel Ninja
NJ!
I am still doubtful. You have mentioned just three entries in the required sheet while there are more highlighted area in that sheet.
What i have understood is that its a schedule for TV and you want only those hours in the required sheet that have something to be telecast-ed in that time slot? for example in 7:00 pm u have 'love ka lya kutch bhe karaga' you want to move it to the other sheet under 1st April @ 7:00 pm? and you want to this for the entire sheet. is that so??
Regards,
Faseeh
I am still doubtful. You have mentioned just three entries in the required sheet while there are more highlighted area in that sheet.
What i have understood is that its a schedule for TV and you want only those hours in the required sheet that have something to be telecast-ed in that time slot? for example in 7:00 pm u have 'love ka lya kutch bhe karaga' you want to move it to the other sheet under 1st April @ 7:00 pm? and you want to this for the entire sheet. is that so??
Regards,
Faseeh
yes only three values from the entire sheet, this is called FPC (fixed point chart), and these values be copied to the second sheet. title of the programs are the ones that appear after the blue highlighted cells, time can be selected from any column preferred, while the date is the one on top of each new sheet.
hope that this is clear
appreciated
NJ
hope that this is clear
appreciated
NJ
SirJB7
Excel Rōnin
Hi, NJ786!
Give a try to this:
=INDICE('dummy data'!A:E;COINCIDIR(A2;'dummy data'!D:D;0)+COINCIDIR(B2;INDIRECTO("'dummy data'!A"&COINCIDIR(A2;'dummy data'!D:D;0)+1&":A65536");0)+1;4)
-----> in english:
=INDEX('dummy data'!A:E,MATCH(A2,'dummy data'!D:D,0)+MATCH(B2,INDIRECT("'dummy data'!A"&MATCH(A2,'dummy data'!D:D,0)+1&":A65536"),0)+1,4)
Regards!
Give a try to this:
=INDICE('dummy data'!A:E;COINCIDIR(A2;'dummy data'!D:D;0)+COINCIDIR(B2;INDIRECTO("'dummy data'!A"&COINCIDIR(A2;'dummy data'!D:D;0)+1&":A65536");0)+1;4)
-----> in english:
=INDEX('dummy data'!A:E,MATCH(A2,'dummy data'!D:D,0)+MATCH(B2,INDIRECT("'dummy data'!A"&MATCH(A2,'dummy data'!D:D,0)+1&":A65536"),0)+1,4)
Regards!
SirJB7
Excel Rōnin
Hi, NJ786!
Maybe I didn't explain myself well. The formula I posted before was for any cell of row 2 in sheet "requirement" and it displays similar values as those posted by you.
Here's the uploaded file:
http://dl.dropbox.com/u/60558749/Extracting%20data%20in%20a%20given%20Tabular%20Format%20to%20a%20new%20sheet%20%28Time%2CDate%2CText%2Cetc...%29%20-%20data%20extraction%20test%20file%20%28for%20NJ786%20at%20chandoo.org%29.xlsx
Just advise if any trouble.
Regards!
Maybe I didn't explain myself well. The formula I posted before was for any cell of row 2 in sheet "requirement" and it displays similar values as those posted by you.
Here's the uploaded file:
http://dl.dropbox.com/u/60558749/Extracting%20data%20in%20a%20given%20Tabular%20Format%20to%20a%20new%20sheet%20%28Time%2CDate%2CText%2Cetc...%29%20-%20data%20extraction%20test%20file%20%28for%20NJ786%20at%20chandoo.org%29.xlsx
Just advise if any trouble.
Regards!
Hi
Well that is just a part of the requirement, now i don't want to manually paste those values from sheet 1 to the next as i have tens of them to be worked on. we need to get the three values from sheet 1 and not from sheet 2 so as to automate the whole process.
Hope i cleared my requirement.
Regards
NJ
Well that is just a part of the requirement, now i don't want to manually paste those values from sheet 1 to the next as i have tens of them to be worked on. we need to get the three values from sheet 1 and not from sheet 2 so as to automate the whole process.
Hope i cleared my requirement.
Regards
NJ
SirJB7
Excel Rōnin
Hi, NJ786!
I'm afraid you didn't. In column D of sheet "requirement", from row 2 thru 4, you have the formulas that you should put in column C (selecting, D2, adjusting column values or pressing F2, selection whole formula, Ctrl-C, pressing Escape, selecting C2, Ctrl-V, ... then copy down as needed). I left them in D just for aiding you when checking values.
What's not clear at all for me is what you don't want to manually paste which values, if the previous formula give each of them to you. You only have to enter data in columns A and B, obviously needed as search argument.
Please try to explain it to me again. Thanks.
Regards!
I'm afraid you didn't. In column D of sheet "requirement", from row 2 thru 4, you have the formulas that you should put in column C (selecting, D2, adjusting column values or pressing F2, selection whole formula, Ctrl-C, pressing Escape, selecting C2, Ctrl-V, ... then copy down as needed). I left them in D just for aiding you when checking values.
What's not clear at all for me is what you don't want to manually paste which values, if the previous formula give each of them to you. You only have to enter data in columns A and B, obviously needed as search argument.
Please try to explain it to me again. Thanks.
Regards!
hi again
you have been really helpful, but what i meant was to get the three values (Date, Time, Title)from the first sheet based on a formula, my formula only pulls the next row/cell combination and does not validate the data pulled against all three values, hence not feasible in this case.
Can you pull the data somehow based on the above criteria and not the data in the second sheet.
Appreciated
NJ
you have been really helpful, but what i meant was to get the three values (Date, Time, Title)from the first sheet based on a formula, my formula only pulls the next row/cell combination and does not validate the data pulled against all three values, hence not feasible in this case.
Can you pull the data somehow based on the above criteria and not the data in the second sheet.
Appreciated
NJ
SirJB7
Excel Rōnin
Hi, NJ786!
I still don't understand. Please tell me how should I extract from sheet "dummy data". columns A:C of sheet "requirement" if column A & B of this last sheet are the arguments for retrieval of the third column from within the first sheet.
If you have a formula, an explanation, or anything alike, please post it here.
I'd appreciate a couple of examples as they should be from your data. Thanks.
Regards!
I still don't understand. Please tell me how should I extract from sheet "dummy data". columns A:C of sheet "requirement" if column A & B of this last sheet are the arguments for retrieval of the third column from within the first sheet.
If you have a formula, an explanation, or anything alike, please post it here.
I'd appreciate a couple of examples as they should be from your data. Thanks.
Regards!
hi
currently i am working on this and have no clue how to pull the data that is there in sheet 1, they are not uniformly placed in the table. First of all the data required from sheet 1 are the three values (Dtae/Time/Title) any thing else is junk for me at the moment.
How to isolate that data on to sheet 2???? is what's holding me.
1 - I can copy paste (date & time)from each sheet's (have multiple sheets for each day) data to refer to later as with your example.
2 - Automate the 2nd sheet to pull the date and time based on a formula and then use you example to pull the third value.
bottom line, i want the date / time / title to be pulled automatically based on some criteria that will verify that all three values fall in the same row, where we can change the time and date (variables)at will to accomodate the new data when ever available.
My sheet only shows the data format required and not the data available for arguments.
appreciate your patience.
regards
NJ
currently i am working on this and have no clue how to pull the data that is there in sheet 1, they are not uniformly placed in the table. First of all the data required from sheet 1 are the three values (Dtae/Time/Title) any thing else is junk for me at the moment.
How to isolate that data on to sheet 2???? is what's holding me.
1 - I can copy paste (date & time)from each sheet's (have multiple sheets for each day) data to refer to later as with your example.
2 - Automate the 2nd sheet to pull the date and time based on a formula and then use you example to pull the third value.
bottom line, i want the date / time / title to be pulled automatically based on some criteria that will verify that all three values fall in the same row, where we can change the time and date (variables)at will to accomodate the new data when ever available.
My sheet only shows the data format required and not the data available for arguments.
appreciate your patience.
regards
NJ
SirJB7
Excel Rōnin
Hi, NJ786!
Now I got what you wanted, but no Excel formula can do it without entering the dates and times as stated previously. With VBA code (macros) it can be done.
Give a look at this file:
http://dl.dropbox.com/u/60558749/Extracting%20data%20in%20a%20given%20Tabular%20Format%20to%20a%20new%20sheet%20%28Time%2CDate%2CText%2Cetc...%29%20-%20data%20extraction%20test%20file%202%20%28for%20NJ786%20at%20chandoo.org%29.xlsm
Press Alt-F8 and run the macro "CajaBoba", it will fill the worksheet of same name with the whole week program schedule.
Hope it was what you were looking for.
Regards!
Now I got what you wanted, but no Excel formula can do it without entering the dates and times as stated previously. With VBA code (macros) it can be done.
Give a look at this file:
http://dl.dropbox.com/u/60558749/Extracting%20data%20in%20a%20given%20Tabular%20Format%20to%20a%20new%20sheet%20%28Time%2CDate%2CText%2Cetc...%29%20-%20data%20extraction%20test%20file%202%20%28for%20NJ786%20at%20chandoo.org%29.xlsm
Press Alt-F8 and run the macro "CajaBoba", it will fill the worksheet of same name with the whole week program schedule.
Hope it was what you were looking for.
Regards!
Great Job
well i have some issues, hope that you can be of help.
find attached the result in the updated file.
https://docs.google.com/open?id=0B5jCQSsK1WJ9M2pRcG9LbmhSajY3RmpvTVRLd0hRZw
1 - if we change any thing (title,date,time columns or rename the function name or sheet name) although maintaining the same within the code also, the screen pops up with a debug window.
2 - at start it worked fine, then after a few tries the macro started giving haphazard results and random events started filling up, some data already uploaded for your perusal.
Appreciated.
Regards
NJ
well i have some issues, hope that you can be of help.
find attached the result in the updated file.
https://docs.google.com/open?id=0B5jCQSsK1WJ9M2pRcG9LbmhSajY3RmpvTVRLd0hRZw
1 - if we change any thing (title,date,time columns or rename the function name or sheet name) although maintaining the same within the code also, the screen pops up with a debug window.
2 - at start it worked fine, then after a few tries the macro started giving haphazard results and random events started filling up, some data already uploaded for your perusal.
Appreciated.
Regards
NJ
SirJB7
Excel Rōnin
Hi, NJ786!
I downloaded your file, run the macro and got 80 rows in last sheet, not 31 as you send within the file. I can't reproduce this error.
About changing title, date, time columns or renaming the function, if everything is done carefully you shouldn't have any error:
a) for renaming the function, just enter the VBA editor and change the name
b) for renaming sheets, do it from Excel, but change the related constant in the code too: ksDummyData and ksCajaBoba
c) for renaming ranged name, do it from Excel, but change the related variable in the code too: ksCajaBobaTable
d) what you shouldn't change so easily (but that can be done changing the code too) is altering the source layout: starting row, columns at left (3), ending row (margin of a 20%), date position in column, title following time, ...
Regards!
I downloaded your file, run the macro and got 80 rows in last sheet, not 31 as you send within the file. I can't reproduce this error.
About changing title, date, time columns or renaming the function, if everything is done carefully you shouldn't have any error:
a) for renaming the function, just enter the VBA editor and change the name
b) for renaming sheets, do it from Excel, but change the related constant in the code too: ksDummyData and ksCajaBoba
c) for renaming ranged name, do it from Excel, but change the related variable in the code too: ksCajaBobaTable
d) what you shouldn't change so easily (but that can be done changing the code too) is altering the source layout: starting row, columns at left (3), ending row (margin of a 20%), date position in column, title following time, ...
Regards!