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

Displaying various Bus routes operating from/to a particular Location

V

VDS

Member
@Dear All,

I have received data containing various bus routes and their Bus No. (Data attached in 2 worksheets). worksheet "Master Copy" contains the bus routes, & Bus No. worksheet Location gives Place, Bus No and route. By pressing Control + F , it displays many times, meeting criteria.

My query is that whenever particular location is entered, all the corresponding rows should display their bus No and route (it may be more than one record) after taking from the master copy.

Is it can be done with any formula ? Any help will be most appreciated.

VDS
 

Attachments

Hi VDS,

For Bus No try below array formula in B2 and copy down:

=IFERROR(INDEX('Main Sheet'!A$2:A$555,SMALL(IF(ISNUMBER(SEARCH($A$2,'Main Sheet'!$B$2:$B$555)),ROW('Main Sheet'!$B$2:$B$555)-ROW('Main Sheet'!$B$2)+1),ROWS(LOCATION!B$2:B2))),"")

And for route try below array formula in C2 and copy down:
=IFERROR(INDEX('Main Sheet'!B$2:B$555,SMALL(IF(ISNUMBER(SEARCH($A$2,'Main Sheet'!$B$2:$B$555)),ROW('Main Sheet'!$B$2:$B$555)-ROW('Main Sheet'!$B$2)+1),ROWS(LOCATION!C$2:C2))),"")

Enter both formula with Ctrl+Shift+Enter.

Regards,
 
@Dear Ashish,

Please dont interrupt into the Title and discussions with your views. Just watch it and learn. For any doubts, note down separately and ask through a different title. This is very good practice to learn new things. In fact I have also done like you earlier. But dont worry practice makes man perfect.

VDS
 
@VDS

Can you confirm whether the formulas meet your requirement.

Second, this is an open forum, where everybody come to learn. So would request @ashish mehra /@VDS not to interrupt anybody from raising their comments or questions related to query, until unless something is commented totally out of context which will be monitored by site administrator. So keep learning.

Regards,
 
@Somendra,

Thanks for ur suggestion & feedback. Still, I am confused wih Iferror and Iserror. Is there any clear funda to differentiate. In fact my version is in excel 2003. I always requesting this forum to convert IFERROR INTO ISERROR. How can In learn it.



VDS
 
@VDS

Pardon me for not remembering this that you have EXCEL 2003.

IFERROR is used on EXCEL 2007+

for 2003 simply convert the formula to

=IF(ISERROR(formula),"",formula)

here formula will be INDEX('Main Sheet'!A$2:A$555,SMALL(IF(ISNUMBER(SEARCH($A$2,'Main Sheet'!$B$2:$B$555)),ROW('Main Sheet'!$B$2:$B$555)-ROW('Main Sheet'!$B$2)+1),ROWS(LOCATION!B$2:B2)))

Copy to the right and down.

Regards,
 
@SM,

I have extracted data with INDEX and it is working. While updating with IF(Iserror (formula), it is not updating. Modified data attached. Still, in the Location worksheet, duplicate entry is coming. Can u check it out ?

VDS
 

Attachments

@ashish mehra

Sorry for the delay. So, here is what I did.

=IFERROR(INDEX('Main Sheet'!A$2:A$555,SMALL(IF(ISNUMBER(SEARCH($A$2,'Main Sheet'!$B$2:$B$555)),ROW('Main Sheet'!$B$2:$B$555)-ROW('Main Sheet'!$B$2)+1),ROWS(LOCATION!B$2:B2))),"")

The formula uses the INDEX function to get say bus number one by one in seperate row.

Red part is the array of Bus number. Now SMALL function is generating an array of row number where the Place name is found in route column. So search function is used to see if each row of route contains the place say Nehru Place, the search function will give a number if it's found in the rows of route in main data, than ISNUMBER will convert these number and errors(if rows don't contains the place) to TRUE & FALSE respectively. So wherever TRUE, IF function will assigned a row number through ROW('Main Sheet'!$B$2:$B$555)-ROW('Main Sheet'!$B$2)+1) part of the formula which will generate a array of {1;2;3;.....545}.

SO Finally SMALL function will have an array of row numbers wherever the place is found on the route, now ROWS(LOCATION!B$2:B2) function will generate 1,2,....and so on as we drag the formula down and SMALL function will give 1st small row number to INDEX and than 2nd and so on. Dragginf the formula down to the row till we get some error will indicate that whole of the data is extracted. To hide error IFERROR is used.
Drag the formula to your requirement.

The same logic is used to extract route also, just changed the Array of INDEX function. And since the array ref. is column relative & row absolute, if you copy this formula to right, you will get the data as in the main data these two things are placed next to each other. So this helps in not writing the whole formula again.

Hope, this makes sense, if you still have doubts just write back.

Regards,
 
Sorry SM for reverting back late.

Thanks SM for detailed explanation.

I have watched couple of Mr. Excel videos on youtube on IF, ISNUMBER & SEARCH to understand more on same.

If possible, please provide me link of few videos on same.

Regards,
AM:)
 
@Somendra,

Thanks a lot for giving clear explanation. Sorry for late reply. I was not able access internet due to some personal problems.

VDS
 
VDS
Please dont interrupt into the Title and discussions with your views. Just watch it and learn
Click to expand...

I feel you are being a little rude and some what arrogant to ashish mehra. He is asking a perfectly legitimate question in the thread, threads are not private they are here for ALL to view and ask questions that relate to the original post and thread, posts and threads do not belong to the posters.

ask through a different title.
Click to expand...

Why, this is the threat containing the possible answer posted by the authour,Somendra, do you expect a new thread to be started every time someone has a question relating to an answer that has been posted but clarification is being asked for!!

Just watch it and learn.
Click to expand...

That is what ashish is trying to do, not be brushed aside as if he is of no consequence.

But dont worry practice makes man perfect.
Click to expand...

Perfection by its very nature is unattainable, but keep trying.

.
 
You must log in or register to reply here.
Back
Top