LinkBack URL
About LinkBacks
Listing smallest value to largest value one by one using formula only pick up From different table (table 1, table 2, table 3) (like a sorting ) Can you help me.
Thanks
Dackson Jose
Listing smallest value to largest value one by one using formula only pick up From different table (table 1, table 2, table 3) (like a sorting ) Can you help me.
Thanks
Dackson Jose
Hi.
Can we safely reference the contiguous range B3:F9 in any solutions? Or are the three ranges strictly non-contiguous?
Regards
Hi,
treat separately not continues please
Thanks.
=IF(ROWS($1:1)>COUNT($B$3:$B$11,$D$3:$D$11,$F$3:$F$11),"",SMALL(($B$3:$B$11,$D$3:$D$11,$F$3:$F$11),ROWS($1:1)))
Copy down as required.
Regards
My friend
Really appreciate your wisdom effort Wonderful!!!!!!!!!!
Can I give you one more critical challenge|||||||
Thank you very much
Dackson jose
You're welcome!
What's the other challenge?
Regards
Please try through this
Like multiple sorting.
Thanks & regards
Dackson Jose
Hi.
Your profile says Excel 2007 but you've attached a 2003 sheet. Can you confirm whether this solution needs to work for 2003?
Regards
Hi,
both Sir
Thanks.
You have precisely 10 rows across your 4 tables which are not "NIL". Is this a coincidence? I mean, do you want to always return just the top ten? Or return however many there are?
Regards
Hi,
This is not ten may be more that otherwise less than that depend on data how come will entered.
And can it be assumed that, if a given row has a numerical value in one column, then it will have numerical values in the three other columns also?
Also, in your example, every entry which is not "NIL" is numerical - is this always the case?
Regards
If you want 'Nil' you can make it 'zero' numeric
Thanks
Sorry - that answered neither of my two questions.
Regards
Hi,
Also, in your example, every entry which is not "NIL" is numerical - is this always the case? : yes always numerical.....
The all cell in the example want to numeric. I just type ‘NIL' because which is no data, so I had reply that you can make it all cell numeric through changing zero.
Thanks, but that only answers my second question.
Forgive me, but I am attempting to put quite a lot of time into a solution on this problem: I get the impression that you don't feel quite as committed to achieving that goal.
Regards
can it be assumed that, if a given row has a numerical value in one column, then it will have numerical values in the three other columns also?
the three other columns also numeric.............
my friend
Thanks your patient Go head............
Unless I'm mistaken, this is an extremely complex solution to what should be a quite straightforward task.
The main reason being that not only are your ranges non-contiguous, but they are each located in a different sheet. What's more, they do not even occupy the same columns within each of those sheets.
I would strongly recommend that you have a re-think about your data set-up. The below solution is far, far more complex than should ever be necessary for even a moderately well-organized Excel workbook.
First go to Name Manager and create:
Name: Table1
Refers to: =TABLE1!$A$3:$D$39
Name: Table2
Refers to: =TABLE2!$E$3:$H$39
Name: Table3
Refers to: =TABLE3!$I$3:$L$39
Name: Table4
Refers to: =TABLE4!$M$3:$P$39
Name: Arry1
Refers to: =INDIRECT(CELL("address",INDIRECT("Table"&N(IF(1,{1,2,3,4})))))
The array formula** in cell A3 of the ANSWER sheet is then:
=LARGE(N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,,,)),QUOTIENT(COUNT(Table1,Table2,Table3,Table4),4)-ROWS($1:1)+1)
And the array formula** in B3:
=SMALL((N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,,,))=$A3)*N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,COLUMNS($A:A),,)),401-COUNTIF($A3:$A$12,$A3))
And in C3:
=SMALL((N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,,,))=$A3)*(N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,1,,))=$B3)*N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,2,,)),401-SUM(($A3:$A$12=$A3)*($B3:$B$12=$B3)))
And in D3:
=SMALL((N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,,,))=$A3)*(N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,1,,))=$B3)*N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,3,,)),401-SUM(($A3:$A$12=$A3)*($B3:$B$12=$B3)))
Copy all down as required.
Note that these formulas calculate over a range of 100 rows beginning at the starting row for each table. If there is data within that range, e.g. another table below one of these tables which falls within that range, then that data too will be considered here, which may of course be undesirable.
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
I want to lie down in your foot and salute you……………
Marvels work….
Really I am very late for register in your excel forum!!!!!! Sorry for that………………
Thank you very much my dear……………….
Can I give you one more challenge?!!!!!!! I don’t know it’s critical or silly work for you…..?
Could I?
Dear Sir,
I am facing a big problem for make trip allowance sheet for each employee, can you help me?
Point below mentioned for your clarification.
This is a logbook of which is going for attend breakdown at outside of the workshop.
The first 5 sheet containing the details of 5 different commercial vehicle logbooks for attend outside running vehicles breakdown.
Each commercial vehicle brings one to five mechanic employees with its each trip for attend breakdown and one day they can attend may be more than one trip with the different commercial vehicle or may be nil.
In a sheet S to X column representative Employee’s code no (who are going to attend breakdown in the vehicle) see the heading EMP 1 to EMP 6. But employee code will be come any cell between S to X column, its mean an employee one day will come EMP 1 cell the next trip may be the code will be EMP 4 cell and the same employee will come the next sheet EMP5 cell.
Each trips every employees they will get trip allowance. It’s depend of their working used timing (less than 3 hours work nothing they will get, 3 to 6 Hours amount will be 10/-, 6 and above hours work will get 15/-)
The sixth sheet me required the report of each employee’s total trip allowance for the current month. When we choose the code of an employee in the report sheet automatically want to display the current selected employee’s total attended trip with the all row details. It will be sorted. See the example of EMP code of B311 in the seventh sheet.
Please help for me…………
Hi.
I think you need to re-post this last one as a new thread in this forum.
Regards
Hi,
Yes .........
Up to this time no one reply for that post........
Regards,
Hi,
Are you doing some thing against BREAKDOWN LOGBOOK FINAL.xls....
Regards,
Hi,
I think answer sheet not finishing in the row of 12, it will be continues. Then how can amend the formula for getting the last row of total counted data.
And the array formula** in B3:
=SMALL((N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,,,))=$A3)*N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,COLUMNS($A:A),,)),401-COUNTIF($A3:$A$12,$A3))
And in C3:
=SMALL((N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,,,))=$A3)*(N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,1,,))=$B3)*N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,2,,)),401-SUM(($A3:$A$12=$A3)*($B3:$B$12=$B3)))
And in D3:
=SMALL((N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,,,))=$A3)*(N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,1,,))=$B3)*N(OFFSET(Arry1,ROW(INDIRECT("1:100"))-1,3,,)),401-SUM(($A3:$A$12=$A3)*($B3:$B$12=$B3)))
Please check Red marked ..............
Unfortunately not, no. As I said, this should be dealt with in a separate post.Originally Posted by dackson
If you already have that post, then you could try "bumping" it.
Regards
I'm sorry I don't know what you mean.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks