LinkBack URL
About LinkBacks
Hi
The attachment has all the details.
This list of data will actually go for about 30000 lines.
Hope you can help
Seven.xlsx
Hi
The attachment has all the details.
This list of data will actually go for about 30000 lines.
Hope you can help
Seven.xlsx
considering you have 30,000 over rows, you might want to copy this formula down in row 2:
=IF(L2<>"",L2,G2)
then try:
Formula:
Please Login or Register to view this content.
or an array formula. i dont know how slow it would take, but try::
Formula:Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
if you dont want to add the additional column, this array formula below would run slower. see if it works:
Formula:Please Login or Register to view this content.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
I'm a bit confused, but it's early a.m.
Q1on your sheet, rows 21-22. Is "2.2.1: 13 Week Full Outcomes" the starting point for your query (the formulae returning the values in the other 3 cells), or is that what you want that group of cells to return?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I have tried the last array formula but it come up as #N/A, any suggestions?
I don't want to add a line to this report, but also ary of the speed and size of the report I am trying to buil.
The Q1 is like step 1, in my head this is the steps the formula would need to take to work.Originally Posted by Glenn Kennedy
i copied the wrong formula:
Formula:Please Login or Register to view this content.
and remove the extra spaces you have in column G and L. "CC " has a space & so does "THE ". if it's supposed to have space, try a little adjustment:
Formula:Please Login or Register to view this content.
=INDEX($A$2:$S$13,IFERROR(MATCH($A22&$A21&$B21,INDEX(TRIM($A$2:$A$13)&" "&TRIM($L$2:$L$13)&" "&TRIM($N$2:$N$13)&TRIM($Q$2:$Q$13)&TRIM($R$2:$R$13),0),0),MATCH($A22&$A21&$B21,INDEX(TRIM($A$2:$A$13)&" "&TRIM($G$2:$G$13)&" "&TRIM($N$2:$N$13)&TRIM($Q$2:$Q$13)&TRIM($R$2:$R$13),0),0)),19)
Try this formula in "B18"
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
I have tried the last array formula but it come up as #N/A, any suggestions?considering you have 30,000 over rows, you might want to copy this formula down in row 2:
=IF(L2<>"",L2,G2)
then try:
Formula:Please Login or Register to view this content.
or an array formula. i dont know how slow it would take, but try::
Formula:Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
if you dont want to add the additional column, this array formula below would run slower. see if it works:
Formula:Please Login or Register to view this content.
I don't want to add a line to this report, but also ary of the speed and size of the report I am trying to buil.
Thanks that works.i copied the wrong formula:
Formula:Please Login or Register to view this content.
and remove the extra spaces you have in column G and L. "CC " has a space & so does "THE ". if it's supposed to have space, try a little adjustment:
Formula:Please Login or Register to view this content.
Would it be better for my report size and speed to use the array formula or a formula that is not an array like Siva has added?
why don't you try it out? i have not tested it exactly the way Siva did it, but to my knowledge, using INDEX to remove the need of pressing CTRL + SHIFT + ENTER actually made speed even slower. here's what i have tested in the past (again, not exactly this scenario)
http://www.excelforum.com/excel-form...-and-cons.html
Ok I will test it out, Thanks
There is one last thing to finish this formula, if A18 can't be found it currently shows up as #N/A, can you change the formula so that any #N/A shows as a 0
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks