Moving a value where two cells meet

Navman

New Member
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.

Navman

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.

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

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

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

Replies
10
Views
418
Replies
2
Views
185
Replies
1
Views
222
Replies
1
Views
176
Replies
7
Views
291

1,203,201
Messages
6,054,088
Members
444,702
Latest member
patrickmg17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back