Index vehicle, driver and vehicle wise revenue report.

HI Sir,
I have full month customer, vehicle and driver wise revenue report, I need to index data from DATA ENTRY sheet to REPORT(ans) sheet. The correct sample data entered in "report(ans)" sheet by manually in Ash colour cells, match with some conditions.

https://wetransfer.com/downloads/128...4043928/07821a


Thanks & Regards
Dackson Jose

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Please post file to this forum as many members will not visit file-hosting sites:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Index vehicle, driver and vehicle wise revenue report.

HI Sir,
I have full month customer, vehicle and driver wise revenue report, I need to index data from DATA ENTRY sheet to REPORT(ans) sheet. The correct sample data entered in "report(ans)" sheet by manually in Ash colour cells, match with some conditions.




Thanks & Regards
Dackson Jose

https://www.excelforum.com/attachmen...1&d=1494758578

in C26

=IFERROR(INDEX('DATA ENTRY'!$B$4:$B$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Veh Status")*('DATA ENTRY'!$A$2:$DV$2=9)*(COLUMN($A$2:$DV$2))))="ON JOB",ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),ROWS($B$4:B4)),0),"")

in E26

=IFERROR(INDEX('DATA ENTRY'!$AK$4:$AK$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Veh Status")*('DATA ENTRY'!$A$2:$DV$2=9)*(COLUMN($A$2:$DV$2))))="ON JOB",ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),ROWS($B$4:D4)),0),"")

Both ...

...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.

You could have the following in A6

='DATA ENTRY'!$B4

Copy down

And formula where 0 might be returned could be simplified by removing test for 0 and CUSTOME formatting cells as 0;;;@

e.g D6

from

=IF(INDEX('DATA ENTRY'!$4:$21,ROWS(D$6:D6),MATCH('REPORT(ans)'!D$5&'REPORT(ans)'!$D$3,'DATA ENTRY'!$A$3:$DV$3&'DATA ENTRY'!$A$2:$DV$2,0))=0,"",INDEX('DATA ENTRY'!$4:$21,ROWS(D$6:D6),MATCH('REPORT(ans)'!D$5&'REPORT(ans)'!$D$3,'DATA ENTRY'!$A$3:$DV$3&'DATA ENTRY'!$A$2:$DV$2,0)))

to

=INDEX('DATA ENTRY'!$4:$21,ROWS(D$6:D6),MATCH('REPORT(ans)'!D$5&'REPORT(ans)'!$D$3,'DATA ENTRY'!$A$3:$DV$3&'DATA ENTRY'!$A$2:$DV$2,0)))

Looking at the data it is only REVENUE columns which are blank.

HI JohnTopley,
THANK YOU FOR YOUR VALUABLE TIME....
I think breakdown veh listing you missed in 1st Section "B", I am expecting that 3rd 4th section will complete soon...

Regards,
Dackson

I think you should able to work that out yourself.

Hint: Look at formula for C26

Ya,
Yes I will work accordingly and update to you soon... But with that someone can try for me...........

REgards
Dackson.

Alright: ...simple change "on Job" to "Break Down" in formula in C26: not that difficult!!!

No Sir,
Not break down listing
I am worried about Section 3, Section 4
Regards,
DAckson.

3rd Section

Q6

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Driver")*('DATA ENTRY'!$A$2:$DV$2=$P6)*(COLUMN($A$2:$DV$2))))=$Q$3,ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),1),($P6-1)*4+1),"")

R6

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Driver")*('DATA ENTRY'!$A$2:$DV$2=$P6)*(COLUMN($A$2:$DV$2))))=$Q$3,ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),1),($P6-1)*4+2),"")

S6

=IFERROR(INDEX('DATA ENTRY'!$B$4:$B$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Driver")*('DATA ENTRY'!$A$2:$DV$2=$P6)*(COLUMN($A$2:$DV$2))))=$Q$3,ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),1)),"")

T6

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Driver")*('DATA ENTRY'!$A$2:$DV$2=$P6)*(COLUMN($A$2:$DV$2))))=$Q$3,ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),1),($P6-1)*4+4),"")

All array entered

4th Section

NOTE: need to add formula in V6 to get list of number of parties per day: NOT yet done

W6

=IFERROR(INDEX('DATA ENTRY'!$B$4:$B$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="PARTY")*('DATA ENTRY'!$A$2:$DV$2=$V6)*(COLUMN($A$2:$DV$2))))=$W$3,ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),(ROWS($1:1)-COUNTIF($V$6:V6,"<" & $V6)))),"")

X6

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="PARTY")*('DATA ENTRY'!$A$2:$DV$2=$V6)*(COLUMN($A$2:$DV$2))))=$W$3,ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),(ROWS($1:1)-COUNTIF($V$6:V6,"<" & $V6))),($V6-1)*4+2),"")

Y6

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="PARTY")*('DATA ENTRY'!$A$2:$DV$2=$V6)*(COLUMN($A$2:$DV$2))))=$W$3,ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),(ROWS($1:1)-COUNTIF($V$6:V6,"<" & $V6))),($V6-1)*4+3),"")

Z6

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="PARTY")*('DATA ENTRY'!$A$2:$DV$2=$V6)*(COLUMN($A$2:$DV$2))))=$W$3,ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),(ROWS($1:1)-COUNTIF($V$6:V6,"<" & $V6))),($V6-1)*4+4),"")

Yes............. You are right the forth section increment number not working balance every thing fine....
Regards..
Dackson

It is not working because there is no formula: tricky one if days have 0 entries.

DON'T WORRY BUT JUST THINK ABOUT THAT i AM SURE THAT YOU WILL GET DYNAMIC FORMULA WITH TOTAL COUNTING OF VEHS AND AMOUNT..
REGARDS
DACKSON

Try this
For section 3. ARRAY formula in P6 and drag across for section 3.

=INDEX('DATA ENTRY'!$B$2:$DV$21,IF(P$5="DATE",1,SMALL(IF(INDEX('DATA ENTRY'!$B$2:$DV$21,,SMALL(IF('DATA ENTRY'!$B$2:$DV$21='REPORT(ans)'!$Q$3,COLUMN('DATA ENTRY'!$B$2:$DV$21),""),ROWS('REPORT(ans)'!$P$6:$P6))-COLUMN($B$2)+1)='REPORT(ans)'!$Q$3,ROW('DATA ENTRY'!$B$2:$DV$21),""),1)-ROW($B$2)+1),IF(P$5="VEH NO",1,SMALL(IF('DATA ENTRY'!$B$2:$DV$21='REPORT(ans)'!$Q$3,COLUMN('DATA ENTRY'!$B$2:$DV$21),""),ROWS('REPORT(ans)'!$P$6:$P6))-IF(P$5="DATE",1,MATCH('REPORT(ans)'!$P$3,'DATA ENTRY'!$B$3:$F$3,0)-MATCH('REPORT(ans)'!P$5,'DATA ENTRY'!$B$3:$F$3,0)+1)))

kvsrinivasamurthy & JohnTopley,
Thanks your valuable time............
For section 3 - Example- If a driver make 2 trips (multi trip) in a same day for different party & different veh, then what formula can apply in increment no column P (Date). Before P6 to P36 continue 1 to 31 day entered for this case same day one or more date will come mean dynamic range... can you try solution for that?.
Regards,
Dackson.

Hi Sir,
in E26 (for on job drivers)

=IFERROR(INDEX('DATA ENTRY'!$AK$4:$AK$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Veh Status")*('DATA ENTRY'!$A$2:$DV$2=9)*(COLUMN($A$2:$DV$2))))="ON JOB",ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),ROWS($B$4:D4)),0),"")
If Instead of "9"(day) apply $D$3 cell then some day not working properly due to for picking the Employees selected AK4:AK21, that same employee row in other days it will work proper other wise the employee gone other row then that days will not work properly.
Please advice for that...
Regards,
Dackson.

Change "9" to reference a cell with required data

If day apply $D$3 cell then some day not working properly due to for picking the Employees selected AK4:AK21, that same employee row in other days it will work proper other wise the employee gone other row then that days will not work properly.
for example please check in 6 day A025 not in the on job But Showing in On job

Please advice for that...
Regards,
Dackson.

in E26

=IFERROR(INDEX('DATA ENTRY'!$A$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Veh Status")*('DATA ENTRY'!$A$2:$DV$2=$D$3)*(COLUMN($A$2:$DV$2))))="ON JOB",ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),ROWS($B$4:D4)),($B$25-1)*4+1),"")


...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.

please check above formula not working my pc.........

what is B25

I changed formula to use D3 (rather B25 in my testing) for Day number but forget to change last part

=IFERROR(INDEX('DATA ENTRY'!$A$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Veh Status")*('DATA ENTRY'!$A$2:$DV$2=$D$3)*(COLUMN($A$2:$DV$2))))="ON JOB",ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),ROWS($B$4:D4)),($D$3-1)*4+1),"")

sorry sir its not working last increment column no which u mention not working in the first day (1).
I am try this way ($D$3*4)+1), working fine now.
Thank you very much your time ....
Now one by one I am going through all your formula. If any help required then Please help that time for any alteration required.
Regards
Dackson...

HI JohnTopley SIR,

IN H26 I NEED "NO DRIVER" AND "NO WORK" TOGETHER DRIVER LIST LIKE ON JOB DRIVER LIST. FOR ON JOB ONLY ONE CRITERIA, BUT IN THIS CASE TWO CRITERIA LIST NEED TO DISPLAYING DOWN IN H26 ANY OPTION FOR THAT?

=IFERROR(INDEX('DATA ENTRY'!$A$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Veh Status")*('DATA ENTRY'!$A$2:$DV$2=$D$3)*(COLUMN($A$2:$DV$2))))="NO DRIVER",ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),ROWS($B$4:D4)),($D$3*4)+1),"") + ONE MORE "NO WORK" DRIVERS LIST.

REGARDS,
DACKSON.

Correction :

E26

=IFERROR(INDEX('DATA ENTRY'!$A$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Veh Status")*('DATA ENTRY'!$A$2:$DV$2=$D$3)*(COLUMN($A$2:$DV$2))))="ON JOB",ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),ROWS($B$4:D4)),($D$3*4+1)),"")

H26 (assume you want driver ?)

=IFERROR(INDEX('DATA ENTRY'!$A$4:$DV$21,SMALL(IF( INDEX('DATA ENTRY'!$A$4:$DV$21,,SUMPRODUCT(('DATA ENTRY'!$A$3:$DV$3="Veh Status")*('DATA ENTRY'!$A$2:$DV$2=$D$3)*(COLUMN($A$2:$DV$2))))={"NO DRIVER","NO WORK"},ROW('DATA ENTRY'!$B$4:$B$21)-ROW($B$4)+1,""),ROWS($B$4:B4)),($D$3*4+1)),"")

both entered with Ctrl+Shift+Enter

Its working brooo............
Thanks very much.......
Regards,
dackson

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi, Mr. John

How are you dear. For increment no of section 3 properly working with below mentioned formula but while selecting one date if employee did 2 trips then first trip same details displaying for the second trip also. can you rearrange this formula for me for getting correct answer. Thanks for your valuable time.

=INDEX('DATA ENTRY'!$B$2:$DV$21,IF(P$5="DATE",1,SMALL(IF(INDEX('DATA ENTRY'!$B$2:$DV$21,,SMALL(IF('DATA ENTRY'!$B$2:$DV$21='REPORT(ans)'!$Q$3,COLUMN('DATA ENTRY'!$B$2:$DV$21),""),ROWS('REPORT(ans)'!$P$6:$P6))-COLUMN($B$2)+1)='REPORT(ans)'!$Q$3,ROW('DATA ENTRY'!$B$2:$DV$21),""),1)-ROW($B$2)+1),IF(P$5="VEH NO",1,SMALL(IF('DATA ENTRY'!$B$2:$DV$21='REPORT(ans)'!$Q$3,COLUMN('DATA ENTRY'!$B$2:$DV$21),""),ROWS('REPORT(ans)'!$P$6:$P6))-IF(P$5="DATE",1,MATCH('REPORT(ans)'!$P$3,'DATA ENTRY'!$B$3:$F$3,0)-MATCH('REPORT(ans)'!P$5,'DATA ENTRY'!$B$3:$F$3,0)+1)))

If you want to cater for multiple events e.g Driver on a given date, then you will need to use the same approach of using helper columns as in Section 4.

Dear John,
Can you provide increment no in a single cell with matching few conditions?
$Q$4 define for month selection example “MAY”
From P6 cell continue in down.
# Count $Q$3 selected driver total qty from all data range $C$4:$DV$21 + total non trip days = that is the maximum count of increment no range.
# Find the month end days (EOMONTH function)
# If the $Q$3 selected driver in single trip in day 1 then increment no need P6 cell 1 P7 cell will be 2. Suppose If the $Q$3 selected driver in multiple trips (qty 3) (Ex: One trip, Two, Three trips) in day 1 then increment no need P6 cell no 1, P7 cell no 1, P8 cell no 1 and P9 will start 2. If the $Q$3 selected driver qty 0 in day 1 then P6 cell no 1, P7 will start 2. Because all day no need to display if the driver doesn’t have trip.
It is very helpful for me Can you gone through this and provide me a good solution.

Day $Q$3 Qty Increment no need in single column
1 2 1
2 1 1
3 1 2
4 1 3
5 0 4
6 1 5
7 1 6
8 1 7
9 1 8
10 2 9
11 1 10
12 0 10
13 1 11
14 1 12
15 1 13
16 1 14
17 1 15
18 1 16
19 1 17
20 3 18
21 1 19
22 0 20
23 0 20
24 0 20
25 1 21
26 1 22
27 2 23
28 1 24
29 2 25
30 2 26
31 2 27
27
28
29
29
30
30
31
31
Regards,
Dackson.

sorry tab not worked while copying from word file please treat last 8 digits increment no only

The attached has helper tables for Sections $ and 3: formulae for section 4 inserted.

You need to copy to Section 3 and change the ranges accordingly.

Is it possible with out helper column in p?

I don't know how this can be done without helper columns but maybe a "guru" will step in with a solution.

Hi JohnTopley,

Thanks for your support.. Please help for solve this.
DAckson

I don't know of a solution which does not use helper columns (as previously provided).

Ok John, Is it possible can you suggest (transfer) me some can solve this problem. Please help...

Dackson

I gave you the solution in the last file I posted.

Now "out of office" for the rest of today.