extracting data with formulas??
- Thread starter diana
- Start date
Sorry! it disappeared somehow
Somendra Misra
Excel Ninja
Hi diana,
Well your problem is not quiet clear.
1. Your thread title says "extracting data with formulas?" but in your sample file you are assigning groups to ID's.
2. There are 2 condition mentioned in file, with a difference of Count of groups. And if both failed then NA condition. Now the relation between condition are not clear (AND , OR relation). Why I am saying this because your sample doesn't meet all the requirements. Can you elaborate on that. Even you mentioned usage in your condition which you did not accounted in your sample.
Lastly, see the attached file.
1. Sheet raw_data has data table, Table1
2. Sheet Output has data extracted as per ID as per your sample output. It also has various condition check based on your condition. But I could not assigned the groups as your conditions are not quite clear. Please go through the calculations.
3. Sheet List has list of Country Groups and Unique ID's, all the three list are mentioned in name manager with dynamic named range so as to handle data when it increase. Go through the name manager.
Regards,
Well your problem is not quiet clear.
1. Your thread title says "extracting data with formulas?" but in your sample file you are assigning groups to ID's.
2. There are 2 condition mentioned in file, with a difference of Count of groups. And if both failed then NA condition. Now the relation between condition are not clear (AND , OR relation). Why I am saying this because your sample doesn't meet all the requirements. Can you elaborate on that. Even you mentioned usage in your condition which you did not accounted in your sample.
Lastly, see the attached file.
1. Sheet raw_data has data table, Table1
2. Sheet Output has data extracted as per ID as per your sample output. It also has various condition check based on your condition. But I could not assigned the groups as your conditions are not quite clear. Please go through the calculations.
3. Sheet List has list of Country Groups and Unique ID's, all the three list are mentioned in name manager with dynamic named range so as to handle data when it increase. Go through the name manager.
Regards,
Hi
Hi Somendra,
I guess I am very confused myself. Really sorry about that...
I have slept on it and re-evaluated everything.
I want to to extract some formulated data from the raw data as well as use what has been formulated to group them accordingly.
Please refer to the attached spreadsheet working_v2, tables and raw. The others u can ignore.
Hi Somendra,
I guess I am very confused myself. Really sorry about that...
I have slept on it and re-evaluated everything.
I want to to extract some formulated data from the raw data as well as use what has been formulated to group them accordingly.
Please refer to the attached spreadsheet working_v2, tables and raw. The others u can ignore.
Somendra Misra
Excel Ninja
Somendra Misra
Excel Ninja
@diana
No need to be Sorry. It's happens with everyone at one point or the other.
Just check the file. It uses raw_data, List Sheet. Output is in Sheet 1. I could not calculate Column H & Column J. Kindly, go throug the comments left in H1 & J1 Cells.
Also go through the Name manager to see all the defined names and their ranges.
If the results, are accurate and you have any diffuculty in understanding any of the formula, write back. Will be happy to guide you.
Note some formulas are array formula, (identification visible {} around formula in formula bar). Array formulas require special key stroke to execute, Ctrl+Shift+Enter. As mentioned in my signature.
Also note that array formulas can slow down your calculations and over all speed of excel performance. So, as your data will grow this method may not suit you and you may wat to switch to VBA or ask here, may be some one can come up with a faster formula.
Regards,
No need to be Sorry. It's happens with everyone at one point or the other.
Just check the file. It uses raw_data, List Sheet. Output is in Sheet 1. I could not calculate Column H & Column J. Kindly, go throug the comments left in H1 & J1 Cells.
Also go through the Name manager to see all the defined names and their ranges.
If the results, are accurate and you have any diffuculty in understanding any of the formula, write back. Will be happy to guide you.
Note some formulas are array formula, (identification visible {} around formula in formula bar). Array formulas require special key stroke to execute, Ctrl+Shift+Enter. As mentioned in my signature.
Also note that array formulas can slow down your calculations and over all speed of excel performance. So, as your data will grow this method may not suit you and you may wat to switch to VBA or ask here, may be some one can come up with a faster formula.
Regards,
Hi Somendra,
Col H, i have come up with a formula already
Thanks for your help. I will take a look over the weekend and come back to you with any questions.
I'm pretty sure I will have some
Actually one question, how to copy the formulas into a new sheet? I cant do it right somehow...
Col H, i have come up with a formula already
Thanks for your help. I will take a look over the weekend and come back to you with any questions.
I'm pretty sure I will have some
Actually one question, how to copy the formulas into a new sheet? I cant do it right somehow...
Somendra Misra
Excel Ninja
Somendra,
Can you please explain to me the formulas which you had used for Group1 and Group2?
=tables!$S$2:INDEX(tables!$S$2:$S$100000,MATCH(TRUE,tables!$S$100000="",0)-1)
I dont quite understand this concept..
Also, I cant seem to get the result for Col B and C even after I input the formula in myself and checked it with yours..it looks exactly the same with the exception of my table name.
thanks
Can you please explain to me the formulas which you had used for Group1 and Group2?
=tables!$S$2:INDEX(tables!$S$2:$S$100000,MATCH(TRUE,tables!$S$100000="",0)-1)
I dont quite understand this concept..
Also, I cant seem to get the result for Col B and C even after I input the formula in myself and checked it with yours..it looks exactly the same with the exception of my table name.
thanks
Somendra Misra
Excel Ninja
Hi diana,
Good to hear you back over here.
The formula you posted above is not right, see below blue portion added.
=tables!$S$2:INDEX(tables!$S$2:$S$100000,MATCH(TRUE,tables!$S$2:$S$100000="",0)-1)
is used to generate dynamic range for Group 1 & Group 2.
INDEX function when used in refrence to another cell refrence return a refrence.
What I mean to say, if you look at the formula tables!$S$2:INDEX..... , so INDEX is in refrence with tables!$S$2.
Now your List of say Group 1 starts with S2 cell on Sheet "tables", and you want the complete list of names in those list to be used in some formula say like a lookup array. So let us assume, at present there are 10 names in the list. So the range of those names will be S2:S12. But what, if you add an extra name in future. You need to change the cell refrences in all the formulas to work.
So the solution is to make a dynamic named range which will update automatically.
So, here INDEX function will return the cell ref. of the cell having last value in the array tables!$S$2:$S$100000. To know the last cell with value, MATCH(TRUE,tables!$S$100000="",0)-1 this part of the formula is used. It is assumed that list will not have blanks in between them, so match will return the row number of first cell having blank in the array S2:S100000.
Say in our example S13 will be blank so match will return 12 as the 12th row in the array is the first blank cell. Now if we -1 from 12 to get the last row with a value in it.
now INDEX function should return the value in that row, but it is used in the ref. of other cell so it will return the ref. to that cell , so the result will be S12.
Column B & Column C are array formulas, so after entering the formula you must enter with Ctrl+Shift+Enter, and not just enter. To see that you had pressed the right keystroke to execute the formula, look at the formula bar, you can see { } around the formulas.
Hope this will clear your doubt as well as issue, if still you require any further assistance write back.
Regards,
Good to hear you back over here.
The formula you posted above is not right, see below blue portion added.
=tables!$S$2:INDEX(tables!$S$2:$S$100000,MATCH(TRUE,tables!$S$2:$S$100000="",0)-1)
is used to generate dynamic range for Group 1 & Group 2.
INDEX function when used in refrence to another cell refrence return a refrence.
What I mean to say, if you look at the formula tables!$S$2:INDEX..... , so INDEX is in refrence with tables!$S$2.
Now your List of say Group 1 starts with S2 cell on Sheet "tables", and you want the complete list of names in those list to be used in some formula say like a lookup array. So let us assume, at present there are 10 names in the list. So the range of those names will be S2:S12. But what, if you add an extra name in future. You need to change the cell refrences in all the formulas to work.
So the solution is to make a dynamic named range which will update automatically.
So, here INDEX function will return the cell ref. of the cell having last value in the array tables!$S$2:$S$100000. To know the last cell with value, MATCH(TRUE,tables!$S$100000="",0)-1 this part of the formula is used. It is assumed that list will not have blanks in between them, so match will return the row number of first cell having blank in the array S2:S100000.
Say in our example S13 will be blank so match will return 12 as the 12th row in the array is the first blank cell. Now if we -1 from 12 to get the last row with a value in it.
now INDEX function should return the value in that row, but it is used in the ref. of other cell so it will return the ref. to that cell , so the result will be S12.
Column B & Column C are array formulas, so after entering the formula you must enter with Ctrl+Shift+Enter, and not just enter. To see that you had pressed the right keystroke to execute the formula, look at the formula bar, you can see { } around the formulas.
Hope this will clear your doubt as well as issue, if still you require any further assistance write back.
Regards,
Last edited:
Hi Somendra,
I think I understand..
As advised I have amended the formula however excel has ran out of resources and cannot calculate it.
What do i do now?
And for col B and C, done that and still no results.. Sigh..
Anyway I have added a new column which is the Total count ID has been in countries from Group 2 only..
The results should be :
81860400 -3
92329917 -4
94885905 -1
96463776 -2
96671779 -3
81689214 -4
Do you think you can help me with this too?
Many Thanks!
I think I understand..
As advised I have amended the formula however excel has ran out of resources and cannot calculate it.
What do i do now?
And for col B and C, done that and still no results.. Sigh..
Anyway I have added a new column which is the Total count ID has been in countries from Group 2 only..
The results should be :
81860400 -3
92329917 -4
94885905 -1
96463776 -2
96671779 -3
81689214 -4
Do you think you can help me with this too?
Many Thanks!
Somendra Misra
Excel Ninja
Hi Diana,
If you can upload your file, than may be somebody can see what is going wrong in it.
Regards,
If you can upload your file, than may be somebody can see what is going wrong in it.
Regards,