Add missing value into cell when criteria are met

[letchimera]

Hi All,

I have a sheet with data and comments added to it. However, this data is changing and new reports need to be run. However, the comment field is the same for the old data.
So i would like find and insert the value in the cells from "Comment" column into the new spreadsheet, as per below:


Old spreadsheet:
[TABLE="width: 642"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Tag[/TD]
[TD]Name[/TD]
[TD]State[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]13/01/2018[/TD]
[TD]05:08:58[/TD]
[TD]Operations[/TD]
[TD]Stefan[/TD]
[TD]ON[/TD]
[TD]In spain has rain again[/TD]
[/TR]
[TR]
[TD]12/01/2018[/TD]
[TD]07:08:17[/TD]
[TD]Banking[/TD]
[TD]Deidra[/TD]
[TD]ON[/TD]
[TD]Door was opened[/TD]
[/TR]
[TR]
[TD]04/01/2018[/TD]
[TD]16:15:27[/TD]
[TD]Consult[/TD]
[TD]Mike[/TD]
[TD]ON[/TD]
[TD]Door was closed[/TD]
[/TR]
</tbody>[/TABLE]

New spreadsheet, where data is missing:

[TABLE="width: 642"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Tag[/TD]
[TD]Name[/TD]
[TD]State[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]13/01/2018[/TD]
[TD]05:08:58[/TD]
[TD]Operations[/TD]
[TD]Stefan[/TD]
[TD]ON[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/2018[/TD]
[TD]07:08:17[/TD]
[TD]Banking[/TD]
[TD]Deidra[/TD]
[TD]ON[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/01/2018[/TD]
[TD]16:15:27[/TD]
[TD]Consult[/TD]
[TD]Mike[/TD]
[TD]ON[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The Date, Time and Tag need to be the Unique Identifier. I was thinking something like IF(A2,B2,C2 found in '1201'! Then insert F"value" in '1301'! Fx)
Any help Is greatly appreciated.

Regards,
Stefan

 

[krogh101]

Hi Stefan!

If you are able to add a helper-column to the old spreadsheet, you could use a simple VLOOKUP or INDEX MATCH?

The helper-column would be something like =Date&Time&Tag and the VLOOKUP would be =VLOOKUP(Date&Time&Tag,[OldSpreadsheet]Book1!$A$2:$G$4,7,0)
(This is based on if the old data is in another workbook, with the helper-column in column A. And the new data is set up from column A1, and the formula in the comment column.)

br.
Henrik

 

[letchimera]

Hi Henrik!

Thank you for the help here!

I let you know if I succeed in setting that up.

KR.
Stefan

Hi Stefan!

If you are able to add a helper-column to the old spreadsheet, you could use a simple VLOOKUP or INDEX MATCH?

The helper-column would be something like =Date&Time&Tag and the VLOOKUP would be =VLOOKUP(Date&Time&Tag,[OldSpreadsheet]Book1!$A$2:$G$4,7,0)
(This is based on if the old data is in another workbook, with the helper-column in column A. And the new data is set up from column A1, and the formula in the comment column.)

br.
Henrik

 

[letchimera]

Not lucky at all! Does someone has a formula for me? Kind Regards, Stefan

 

[krogh101]

Stefan,

Would you be willing to share the workbook(s)? Then I'll happily take a look at it.