SOLVED

Merging tables together

Copper Contributor

Hi everyone, 

I am wanting one table to read from another. 

Table 1

Dee1234910_1-1684176146435.png

J4 which is highlighted I would like to have a "1" in it if they have a 1 in any of the columns in j55-n55 ( the top column) of table 2, even if they have multiple 1's on that row still only show a "1" in Table 1. However if they have 0 then it show a "0" in table 1. If row has both, the 1 overrides.
Table 2. 

Dee1234910_0-1684176073306.png

Thank you in advance. 

 

4 Replies

@Dee1234910 

=IF(SUM(N(ISNUMBER(SEARCH(1,'table 2'!J55:N55)))),1,IF(SUM(N(ISNUMBER(SEARCH(0,'table 2'!J55:N55)))),0,""))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

The tables are on the same sheet, would that effect the formula?
best response confirmed by Hans Vogelaar (MVP)
Solution

@Dee1234910 

=IF(SUM(N(ISNUMBER(SEARCH(1,J55:N55)))),1,IF(SUM(N(ISNUMBER(SEARCH(0,J55:N55)))),0,""))

You can try this formula. This works in my sample file if both tables are on the same sheet.

 

Thank you
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Dee1234910 

=IF(SUM(N(ISNUMBER(SEARCH(1,J55:N55)))),1,IF(SUM(N(ISNUMBER(SEARCH(0,J55:N55)))),0,""))

You can try this formula. This works in my sample file if both tables are on the same sheet.

 

View solution in original post