Moving a value where two cells meet

[Navman]

Hi all,

The setup is as follows: In Sheet1 I have a list of ID's in column A (A2:A10)and in Row 1 (B1:G1) I have list of dates.

In sheet2 I have imported data from a text file where in column A it has some ID's, column B the date that ID was logged and column C the time it was logged. e.g -> In sheet2, cell A1 -> 12345, Cell B1 -> 22/11/05, Cell C1 -> 10:35.

Now the part I'm really stuck! How do I search sheet1 for an ID match and a date match from sheet2 (match for A1 and B1) and where both cells meet in sheet1 insert the third variable.

e.g. If cell A1 from Sheet2 matches cell A3 in Sheet1 and cell B1 matches cell G1 in Sheet1, Insert the time (C1) where both meet , which is G3. And add a loop so that it does this for all the imported data from Sheet2.

Hope this makes sense. I need to search for a match for two variables and insert the third (time) variable where both meet. Like a timetable.

Please, please help! If you want and e-mail of the workbook let me know. I need this ASAP.

Navman

 

[tusharm]

Suppose you name the ranges in sheet2 as IDs, Dates, and Times. Then, in sheet1 B2 enter the array formula =MAX((IDs=Sheet1!$A2)*(Dates=Sheet1!B$1)*Times) Note the use of absolute and relative addresses. Copy B2 as far across and down as needed.

An array formula is entered *not* by pressing the ENTER key but the combination of CTRL, SHIFT, and ENTER.

 

[neofytosc]

You may also try the Conditional Sum Wizard
=SUM(IF(date_range=B$1;IF(id_range=$A2;time_range;0);0))

and CTRL, SHIFT, and ENTER

Regards

 

[Navman]

THANK YOU VERY VERY VERY MUCH 'tusharm', it worked like a charm. Unfortunately 'neofytosc' I couldn't get yours to work even after renaming the IDs, Dates, Times ranges! But thanks anyway.

OK !

The thing is that I will continuously import data into the same columns in Sheet2. How do I keep the existing time values in Sheet1 and just append the new data imported i.e. Use Sheet2 as a buffer just to move data to Sheet1, while retaining the data values passed in Sheet1 each time. Or better still MOVE the actual time values rather than refer to them.
(Is there a better way to do what I'm trying to do!, since I'm making this for a novice that can't rename and define the ranges IDs, Dates, Times etc. each time he imports data from a text file, since the ranges get cleared?)

Also, code so that if there isn't an instance where an ID and Date have a time in Sheet2 that a default 'Absent' value is inserted/displayed and the cell also goes RED where both correspond in Sheet1. I'm thinking this may need to be done in VBA.

Much Appreciated.
Navman

 

[Navman]

I got neofytosc's code to work. It should be as follows

=SUM(IF(date_range=B$1,IF(id_range=$A2,time_range,0),0))

and CTRL, SHIFT, and ENTER

Neo basically used ; (semi-colons) instead of , (commas).

Regards
_________________

Navman