Excel - Match Data if Excel 1 is between Date and Time of Excel 2

[Vincent518]

I am having problem matching 2 of these data. This data shows Admission and Payment in separate excel sheet. My purpose is to search out for those admission without payment.

The date and time at "Payment Date" if payment was made, will be always in between the date and time in "Admission Data".

I wish to combine the both by matching if data in "Payment data" the time is between the Entry and Exit time in "Admission Data", the data from column A to column C of the "Payment Data" will be copied and aligned to "Admission Data" column E to column G respectively. With this, I will then be able to see which row at the "Admission Data" are without payment.

Conditional formatting to search duplicate value of both column A is not feasible since one person can enter and exit multiple times within a day.

I am processing about over a ten over thousand of row for both Admission and Payment hence the need to use formula or conditional formatting or other methods to achieve my purpose. Is there anyone can help me with this?

 

[arthurbr]

I suppose Excel1 and Excel 2 refer to sheets?

 

[SanjayGMusafir]

What I have understood is that you want to bring Payment Daya information to Admission data sheet for comparison and other purposes.

A simple VLOOKUP function in Column E & F of admission data can pull that data from Payment Data to Admission Data

where LOOKUP Value shall be Name (A4) and return array being Payment time and Payment amount

 

[Vincent518]

What I have understood is that you want to bring Payment Daya information to Admission data sheet for comparison and other purposes.

A simple VLOOKUP function in Column E & F of admission data can pull that data from Payment Data to Admission Data

where LOOKUP Value shall be Name (A4) and return array being Payment time and Payment amount

Will the suggested method by you help when it comes to multiple rows of the same name in column A of my "Admission Data"?

 

[Vincent518]

I suppose Excel1 and Excel 2 refer to sheets?

Different Sheets or different Excel file between the two doesn't matter right?

 

[SanjayGMusafir]

Different Sheets or different Excel file between the two doesn't matter right?

That shall be not the problem

The bigger challenge will be pulling multiple data from Payment data with Excel 2016 platform.

Honestly VLOOKUP will not serve the intended purpose. And with a big database like you are talking about (10000+ rows) there has to a good automated system. Let me think over it. Till that time some Expert from Form might suggest a better way - That I can also learn.

I shall think over and revert.

 

[SanjayGMusafir]

View attachment 71610

View attachment 71609

I am having problem matching 2 of these data. This data shows Admission and Payment in separate excel sheet. My purpose is to search out for those admission without payment.

The date and time at "Payment Date" if payment was made, will be always in between the date and time in "Admission Data".

I wish to combine the both by matching if data in "Payment data" the time is between the Entry and Exit time in "Admission Data", the data from column A to column C of the "Payment Data" will be copied and aligned to "Admission Data" column E to column G respectively. With this, I will then be able to see which row at the "Admission Data" are without payment.

Conditional formatting to search duplicate value of both column A is not feasible since one person can enter and exit multiple times within a day.

I am processing about over a ten over thousand of row for both Admission and Payment hence the need to use formula or conditional formatting or other methods to achieve my purpose. Is there anyone can help me with this?

Can you post the above data using XL2BB utility. I think I got a way out but for that I need to test that on a dummy data. If you can post the above sample data using XL2BB, that will save my lots of Energy and effort.

 

[Vincent518]

That shall be not the problem

The bigger challenge will be pulling multiple data from Payment data with Excel 2016 platform.

Honestly VLOOKUP will not serve the intended purpose. And with a big database like you are talking about (10000+ rows) there has to a good automated system. Let me think over it. Till that time some Expert from Form might suggest a better way - That I can also learn.

I shall think over and revert.

There isn't unique identifier in both different sheets here, so I suppose vlookup could not be done?

I am thinking along like if the formula of column E to G of "Admission Data" can identify column B of the "Payment Data" is between column B & C of "Admission Data", then copy all characters of column A to C of "Payment Data" to column E to G of "Admission Data". I do not know how to attain that.

 

[SanjayGMusafir]

There isn't unique identifier in both different sheets here, so I suppose vlookup could not be done?

I am thinking along like if the formula of column E to G of "Admission Data" can identify column B of the "Payment Data" is between column B & C of "Admission Data", then copy all characters of column A to C of "Payment Data" to column E to G of "Admission Data". I do not know how to attain that.

Only when you post a sample data, I can think of a solution.

 

[Vincent518]

Can you post the above data using XL2BB utility. I think I got a way out but for that I need to test that on a dummy data. If you can post the above sample data using XL2BB, that will save my lots of Energy and effort.

Sorry I am quite an amateur when it comes to excel. I do know what is XL2BB by the way