• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index customer and vehicle wise revenue report.

Dackson

Member
HI Sir,
I have full month customer wise revenue report, I need to index data from DATA ENTRY sheet to REPORT(ans) sheet with above mentioned condition in test excel file.

Thanks & Regards
Dackson Jose
 

Attachments

Dackson

Member
Maybe like this? I don't completely understand your data.
Thanks you very much for attending my problem.
Please check the first section of Day wise indexing with out the support of additional increment no(means increment row no inbuilt the formula only) . Also check the "on job vehicle" dynamic list against the above mentioned day, on job driver list against the above mentioned day, breakdown vehicle no list against the above mentioned day.
2nd section vehicle part absolutely correct sir. But the 3rd section driver part not correct due to for finding the rows of driver you took CW4:CW21 that logic i think not working, also in the place of veh no position displaying the party. Please check the party wise list the all month.

Thank you very much again and I am waiting for your correct answer.

Regards,
Dackson.
 

Attachments

Luke M

Excel Ninja
I'm afraid I don't understand what you are asking. :(

for CW:CW21, all your driver columns were identical, so I didn't think it mattered which one we looked at. Is this not the case in your actual data?

In the future, I'd strongly suggest changing your raw data to be more tabular; like this:

upload_2017-5-12_14-51-8.png

the biggest problem with data mining the current layout is that Day is a horizontal field while all the others are vertical.
 

Dackson

Member
Hi Luke,

Thank you very much for spend for me your valuable time....

In example file mentioned only 18 vehicle But my actual data is near 200 vehicles. So in vertical day data not convenient for me. I hope you will get the good solution, which i previously sent to you and waiting for the same.
Regards,
Dackson
 

Luke M

Excel Ninja
How about your take the first file, but ill in (manually) what you would like to see in the cells? That would help me understand better what you mean. Right now, I've no idea what you are explaining here:

Please check the first section of Day wise indexing with out the support of additional increment no(means increment row no inbuilt the formula only) . Also check the "on job vehicle" dynamic list against the above mentioned day, on job driver list against the above mentioned day, breakdown vehicle no list against the above mentioned day.
2nd section vehicle part absolutely correct sir. But the 3rd section driver part not correct due to for finding the rows of driver you took CW4:CW21 that logic i think not working, also in the place of veh no position displaying the party. Please check the party wise list the all month.
 

bosco_yip

Excel Ninja
Maybe.........

1] Table 1 "Vehicle No"

1.1] In K6, formula copy across and down :

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,MATCH($K$3,'DATA ENTRY'!$B$4:$B$21,0),MATCH($J6,'DATA ENTRY'!$C$2:$DV$2,0)+(COLUMN(A1)-2)+1),"")

2] Table 2 "DRIVER"

2.1] In Q6, formula copy across to R6, T6 and all copy down :

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,MATCH($Q$3,INDEX('DATA ENTRY'!$C$4:$DV$21,0,MATCH(1,INDEX(($P$3='DATA ENTRY'!$C$3:$DV$3)*($P6='DATA ENTRY'!$C$2:$DV$2),0),0)),0),MATCH(1,INDEX(($P$3='DATA ENTRY'!$C$3:$DV$3)*($P6='DATA ENTRY'!$C$2:$DV$2),0),0)+MATCH(Q$5,{"PARTY","VEH STATUS","DRIVER","REVENUE"},0)-3),"")

2.2] In S6, formula copy down :

=IFERROR(INDEX('DATA ENTRY'!$B$4:$B$21,MATCH($Q$3,INDEX('DATA ENTRY'!$C$4:$DV$21,0,MATCH(1,INDEX(($P$3='DATA ENTRY'!$C$3:$DV$3)*($P6='DATA ENTRY'!$C$2:$DV$2),0),0)),0)),"")

3] Table 3 "PARTY"

3.1] In W6, formula copy down :

=IFERROR(INDEX('DATA ENTRY'!$B$4:$B$21,MATCH($W$3,INDEX('DATA ENTRY'!$C$4:$DV$21,0,MATCH(1,INDEX(($V$3='DATA ENTRY'!$C$3:$DV$3)*($V6='DATA ENTRY'!$C$2:$DV$2),0),0)),0)),"")

3.2] In X6, formula copy across to Y6, Z6 and all copy down :

=IFERROR(INDEX('DATA ENTRY'!$C$4:$DV$21,MATCH($W$3,INDEX('DATA ENTRY'!$C$4:$DV$21,0,MATCH(1,INDEX(($V$3='DATA ENTRY'!$C$3:$DV$3)*($V6='DATA ENTRY'!$C$2:$DV$2),0),0)),0),MATCH(1,INDEX(($V$3='DATA ENTRY'!$C$3:$DV$3)*($V6='DATA ENTRY'!$C$2:$DV$2),0),0)+MATCH(X$5,{"VEH STATUS","DRIVER","REVENUE"},0)),"")

Regards
Bosco
 

Attachments

Dackson

Member
Hi Luke & bosco_yip,
Please find the revised test file for getting more clarity of you. Now clearly mentioned in "report(ans)" sheet match with above conditions by manually entered the actual data (Ash colour cells).

Regards,
Dackson
 

Attachments

vletm

Excel Ninja
Dackson
just testing ...
1st, 2nd & 3rd Section with (shorter) formulas
(with some help cells/columns ... which can do harder way too)
4th Section ... maybe easier to do with VBA
... just use Your [W3]-drop down
> Ideas ... Questions?
 

Attachments

Dackson

Member
Hi Sir,
Thanks for your kind attention for checking my problem. I am extremely sorry because clearly i cant apply this formula to my actual file and I can't change the file extension also, that mean VBA and macros file not supportable against this challenge. Second thing at the starting me mentioned that "indexing with out the support of additional increment no(means increment row no inbuilt the formula only)". So I am waiting the more good one...
Regards,
Dackson
 

vletm

Excel Ninja
Dackson
Okay - things can do many ways -
why to write long formulas?
with 4th Section You would have great formula with 200 vehicles
and no extra spare formula rows (even now used number of rows vary ... a lot).
... and that 'total' row ... yes!
... maybe someday You'll solve Your problem
- good luck.
 
Top