To go down the row, cell by cell and look for this and return the value of adjacent cell

[brandonnewbie]

Hello, I was hoping to get some help with regards to excel formula.

I have a table containing names of passengers in the first column (on the left).
On the second column, i have the bus plate number. These are the plate number (registration plate number) of the buses which were boarded by the passengers.
On the third column, i have time, which is in chronological order, the most recent timing is at the top, and the older timing are at the bottom.

Every morning, when a passenger boards the bus, the system records the name of the passenger, the number plate of the bus and the time of this boarding as a row entry.
When more passengers board the bus, it does the same and add rows above this. The table is being populated with newer entries from the bottom to the top (ie this is chronological, with newer entries at the top).

When passenger alights from the bus, the system records the name of the passenger (this name is the same as the name of the passenger who had boarded the bus earlier) but it does not record the licence plate number of the bus since the system was previously designed in a manner where it thinks the user (ie someone like me who is now using the system) , would be able to use my cognitive ability and look into the table to discover which bus this particular passenger had boarded. Of course, it also records the time when this passenger alighted from the bus.

There are approximately 300 different passengers and there are 16 different buses. So during the day, from morning till the evening, there are all these 300passengers boarding and alighting these 16buses and all their boarding and alighting are captured on this single excel sheet/table.

The problem i face now is that I have to use my eyes to look at the cells which are blank on column two (this is the bus licence plate column) and if these are blank, I will need to look and find where this passenger had alighted from (since the system does not record the bus license plate when they alight, only when boarding).

What can I do to get a formula to enter these? Basically, if the cell contains a bus license number, it does not do anything. Then it move one cell down (vertically) and look for the next cell below. Again, if this cell is populated, it does nothing. Repeat until the cell is empty. Then it needs to look at the passenger's name (on column 1), and then find this passenger's name again in the same column 1 and look for this same name again....sequentially. It should be able to see this passenger's name again since this would then be the boarding. And the bus licence plate number should be shown. I need to extract this and use this to populate the blank cell so that I can have a holistic and complete table where all the alighting also have the corresponding bus license plate number.


Thanks for your help!

[bebo021999]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). 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.

[brandonnewbie]

Hi There. Here is a before and after. I had to do this manually but i hope to get a method to do this using formula

[Andy Pope]

One way, add a new column which will contain derived bas stage.
In first row enter this and copy down.

=IF([@Bus]<>"",[@Bus],INDEX(B3:$B$232,MATCH([@Name],A3:$A$232,0)))

The example file also includes your after data set so you can validate result.

This will only work if the Name field contains unique names. By that I mean the Dwayne records all relate to the same Dwayne.