0

Working with 2 separate data sets (with duplicates) Dataset is unique identified by an ID. There may not be an entry for the timestamp I require. Datasets are quite large, and due to duplicates, can't use vlookup.

Samples:

Table 1:

    Device Name|Time Bracket| On/Off?
    ID1        |06:20:00    | 
    ID2        |06:20:00    |
    ID3        |06:30:00    |

Table 2:

    Device Name |Timestamp  |On/Off?
    ID1         |06:20:00   |On
    ID2         |06:50:00   |Off
    ID3         |07:20:00   |Off

What I want to achieve:

I want an if statement to check if: 1) device ID matches AND 2) timestamp matches

If so, return the value of On/Off from Table 2. If not, then I want it to return the value of the cell above it IF it's the same device, otherwise just put "absent" into the cell.

I thought I could do this with some IF statements like so:

    =if(HOUR([@[Time Bracket]]) = HOUR(Table13[@[Timestamp Rounded (GMT)]]) and 
    minute([@[Time Bracket]]) = minute(Table13[@[Timestamp Rounded (GMT)]]) and 
    [@[Device Name]]=Table13[@[Device Name]], Table13[@[On/Off?]], 
    IF([@[Device Name]]=Table13[@[Device Name]], INDIRECT("B" and Rows()-1), "absent"))

(I put some newlines in there for readability)

However, this doesn't seem to resolve at all... what am I doing wrong? Is this even the correct way of achieving this?

I've also tried something similar with a VLookUp, but that failed horribly.

Thanks all!

R.Sama
  • 103
  • 12
  • You would want to use INDEX/MATCH see here http://stackoverflow.com/questions/18767439/lookup-using-index-and-match-with-two-criteria There are two methods look at the second. – Scott Craner Jun 14 '16 at 15:21
  • Thanks, let me have a look at this... – R.Sama Jun 15 '16 at 10:34
  • Hey thanks, I've done an implementation of the concatenation/helper column, and this seemed to work! – R.Sama Jun 15 '16 at 13:28

2 Answers2

0

To not deal with array formulas or merging strings which, (not in your case) can still be wrong at the end, I suggest the use of COUNTIFS due to the fact, you have a very small amount of outcomes (just on or off)...

for the first table (starting at A1, so the formula is at C2):

=IFERROR(CHOOSE(
 OR(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"On"))+
 OR(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"Off"))*2
 ,"On","Off","Error"),IF(A1=[@[Device Name]],C1,"Absent"))

this will also show "Error" of a match for "On" and "Off" is shown... to skip that and increase the speed, you also could use:

=IF(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"On"),"On",
 IF(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"Off"),"Off",
 IF(A1=[@[Device Name]],C1,"Absent")))

For both the "Device Name" is at column A, "Time Bracket" at column B and "On/Off?" at column C while the table starts at row 1... If that is not the case for you, then change A1 and C1 so they match

(Also inserted line-breaks for better reading)

Picture to show the layout:

layout

I picked the second formula to show how it works... also, this formula should not be able to return 0's... I'm confused

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
  • Hey, thanks for coming back to me. I've tried both, but I'm getting a bunch of 0s... So for both, I've set the columns to be Device ID | Timestamp/bracket | on/off. My table has headers, so I've changed this to: =IF(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"On"),"On",IF(COUNTIFS(Table13[Device Name],[@[Device Name]],Table13[Timestamp],[@[Time Bracket]],Table13[On/Off?],"Off"),"Off",IF(A2=[@[Device Name]],C2,"Absent"))) Not sure what's happening. – R.Sama Jun 15 '16 at 11:09
  • @R.Sama not sure why that happens... I've tested it, and it worked without any errors... also added a pic to the answer... also pls check if there are any "hidden" characters like leading spaces and all that stuff... – Dirk Reichel Jun 15 '16 at 13:20
  • Ah - I've found (one of) the issues with using this now - the formatting of the cells - because I have disparate data coming in from all sides, it's difficult to maintain the proper formatting on cells, meaning that sometimes, even though the displayed value is correct, the way that Excel interprets it isn't. On trying this on a simple, self created dataset, it does indeed work! – R.Sama Jun 15 '16 at 13:33
0

Couple of good suggestions, however using the helper column as suggested in the topic by Scott Craner above worked. Created a helper column of concat'd device ID and timestamp for both tables, then did a simple VlookUp.

Another lesson learned: Think outside of the box, and go with simple solutions, rather than try + be too clever like I was doing... :)

R.Sama
  • 103
  • 12