Get correspondence of the first (or second or last) word in a table

[Bassplayer]

I'm strugling with a problem. I can get the first correspondence in row, or the last value in a row but what I want is to get a correspondence within a table. I will give an example:
Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]01-jan
[/TD]
[TD]02-jan
[/TD]
[TD]03-jan
[/TD]
[TD]04-jan
[/TD]
[TD]05-jan
[/TD]
[TD]06-jan
[/TD]
[/TR]
[TR]
[TD]PersonA
[/TD]
[TD]Contract A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Contrat B

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]PersonB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Contract A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PersonC
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Contract B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PersonB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Contract C
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Contract A
[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]Contract B
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Contract C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For example, if I put Contract A in 01-Jan and 05-Jan, it should appear on the table of Sheet2 the date 01-jan on the first "x" in the Contract A row (the x is just where a formula shoud be, it doesn´t have to be an X) and 05-Jan on the second X.

Is it possible to get this working like this? Thanks in advance. I´ve tried with index, vlookup etc but i´m not getting it.

Cheers

 

[James006]

Hi,

In Sheet 2 , cell B2, you can test following Array Formula .

=IF(INDEX(MMULT(TRANSPOSE(ROW(Sheet1!$B$2:$G$5)^0),--(Sheet1!$B$2:$G$5=$A2)),COLUMN()-1)=1,"X","")

Hope this will help

 

[Yongle]

Formula in Sheet2, cell B2 copied down and across is
=IF(COUNTIF(Sheet1!B$2:B$5,$A2)>0,"X","")


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td]

01-Jan​

[/td][td]

02-Jan​

[/td][td]

03-Jan​

[/td][td]

04-Jan​

[/td][td]

05-Jan​

[/td][td]

06-Jan​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]PersonA[/td][td]

Contract A​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Contract B​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]PersonB[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Contract A​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]PersonC[/td][td]

​

[/td][td]

​

[/td][td]

Contract B​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]PersonB[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

Contract C​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td]

01-Jan​

[/td][td]

02-Jan​

[/td][td]

03-Jan​

[/td][td]

04-Jan​

[/td][td]

05-Jan​

[/td][td]

06-Jan​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Contract A[/td][td]

X​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

X​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Contract B[/td][td]

​

[/td][td]

​

[/td][td]

X​

[/td][td]

​

[/td][td]

​

[/td][td]

X​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Contract C[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

X​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[Yongle]

or use MATCH and OFFSET like this
Formula in cell B2 copied down and across
=IF(COUNTIF(OFFSET(Sheet1!$B$2:$B$5,0,MATCH(B$1,Sheet1!$B$1:$G$1,)-1,,),$A2)>0,"X","")


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td]

01-Jan​

[/td][td]

02-Jan​

[/td][td]

03-Jan​

[/td][td]

04-Jan​

[/td][td]

05-Jan​

[/td][td]

06-Jan​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Contract A[/td][td]

X​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

X​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Contract B[/td][td]

​

[/td][td]

​

[/td][td]

X​

[/td][td]

​

[/td][td]

​

[/td][td]

X​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Contract C[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

X​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

 

[Bassplayer]

Thanks for the awnsers but I probably didn´t explain myself in the best way. The code displayed works well and will be very usefull to me so thanks to everybody. But what I want in this specific is not to show a X but the date. I will give and example bellow:

Table A in Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]01-jan
[/TD]
[TD]02-jan
[/TD]
[TD]03-jan
[/TD]
[TD]04-jan
[/TD]
[TD]05-jan
[/TD]
[TD]06-jan
[/TD]
[/TR]
[TR]
[TD]Person A
[/TD]
[TD]Contract A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Contract C
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person B
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Contract B
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Contract B
[/TD]
[/TR]
[TR]
[TD]Person C
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Contract A
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What should appear in the other tables are the dates that are in the top row

Table B in Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Contract A
[/TD]
[TD]01-Jan
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]06-Jan
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Contract B
[/TD]
[TD]03-Jan
[/TD]
[TD][/TD]
[TD]06-Jan
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Contract C
[/TD]
[TD][/TD]
[TD]05-Jan
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The X's that I displayed is where the formulas/functions shoud be. For exampel in TableB for the Contract A Row, the first collumn shoud display the first date apointed for contract A in Table B and the fifth column shoud display the second date apointed for contract A.

 

[Yongle]

I understand that the date should be displayed

Please explain the logic to determine the column in which to display it

 

[James006]

Hello,

To get the Dates displayed in Sheet2 :

In Sheet2 - cell B2 the following Array Formula :

=IF(INDEX(MMULT(TRANSPOSE(ROW(Sheet1!$B$2:$G$5)^0),--(Sheet1!$B$2:$G$5=$A2)),COLUMN()-1)=1,Sheet1!B$1,"")

HTH

 

[Bassplayer]

Hello,

To get the Dates displayed in Sheet2 :

In Sheet2 - cell B2 the following Array Formula :

=IF(INDEX(MMULT(TRANSPOSE(ROW(Sheet1!$B$2:$G$5)^0),--(Sheet1!$B$2:$G$5=$A2)),COLUMN()-1)=1,Sheet1!B$1,"")

HTH

Thanks for the reply but i'm getting an error. I'm trying to figure it out what it's wrong.
In sheet2 there shouldn´t exist a row with dates, only in sheet1.

 

[James006]

For An Array Formula ...

Do not use the standard Enter key ...

You need to use simultaneously the three Keys : Control Shift Enter ...

HTH

 

[Bassplayer]

For An Array Formula ...

Do not use the standard Enter key ...

You need to use simultaneously the three Keys : Control Shift Enter ...

HTH

Thanks! But now I´m getting the same date for all fields. In the formula the part "...1,Sheet1!B$1,"") " shouldn´t be locked to the cell B1 but to the cell above. In this case B&variable.