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

Bassplayer

New Member
Joined
Oct 22, 2013
Messages
11
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
01-jan
02-jan
03-jan
04-jan
05-jan
06-jan
PersonA
Contract A
Contrat B

<tbody>
</tbody>
PersonB
Contract A
PersonC
Contract B
PersonB
Contract C

<tbody>
</tbody>

Sheet2
Contract A
x
x
x
Contract B
x
x
Contract C
x

<tbody>
</tbody>

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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi,

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

Code:
=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
 
Upvote 0
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
A
B
C
D
E
F
G
1
01-Jan​
02-Jan​
03-Jan​
04-Jan​
05-Jan​
06-Jan​
2
PersonA
Contract A​
Contract B​
3
PersonB
Contract A​
4
PersonC
Contract B​
5
PersonB
Contract C​
Sheet: Sheet1

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
1
01-Jan​
02-Jan​
03-Jan​
04-Jan​
05-Jan​
06-Jan​
2
Contract A
X​
X​
3
Contract B
X​
X​
4
Contract C
X​
Sheet: Sheet2
 
Upvote 0
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
A
B
C
D
E
F
G
1
01-Jan​
02-Jan​
03-Jan​
04-Jan​
05-Jan​
06-Jan​
2
Contract A
X​
X​
3
Contract B
X​
X​
4
Contract C
X​
Sheet: Sheet4
 
Upvote 0
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
01-jan
02-jan
03-jan
04-jan
05-jan
06-jan
Person A
Contract A
Contract C
Person B
Contract B
Contract B
Person C
Contract A

<tbody>
</tbody>

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

Table B in Sheet2
Contract A
01-Jan
06-Jan
Contract B
03-Jan
06-Jan
Contract C
05-Jan

<tbody>
</tbody>


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.
 
Upvote 0
I understand that the date should be displayed

Please explain the logic to determine the column in which to display it
 
Last edited:
Upvote 0
Hello,

To get the Dates displayed in Sheet2 :

In Sheet2 - cell B2 the following Array Formula :

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

HTH
 
Upvote 0
Hello,

To get the Dates displayed in Sheet2 :

In Sheet2 - cell B2 the following Array Formula :

Code:
=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.
 
Last edited:
Upvote 0
For An Array Formula ...

Do not use the standard Enter key ...

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

HTH
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top