Haven't been able to cobble together an answer for this from previous questions, thanks in advance to anyone who can help. Example data set follows with question below.
<tbody>
</tbody>
Column 5 (PNT_MATCH) is what I want to populate using a formula, in the example I've typed in the values for illustration. I have thousands of lines of data, hence why I can't type in all needed values manually.
Each row of data belongs to a Group (column 1), and the first row of each new group has the value 500 in column 2 (Value). For all non-500 Value rows within each distinct group, I want the cell in column 5 (PNT_MATCH) to return the value from the 500 row's column 4 (PNT_NO), if the address in that given row matches the address in the 500 row. If the address does not match, NA should be returned.
So as illustrated above, the address in Row 3 matches the address in Row 2 (which is a 500 PCT_MATCH row), so the value of Row 2's PNT_NO is returned in Row 3's PNT_MATCH column (11164). The address in Row 4 does not match so NA is returned. Row 5 represents a new Group (Group 3) so the pattern repeats - Row 6's address does not match Row 5's so NA is returned, then Rows 7 and 8's addresses do match Group 3's 500 Value row so the value of Row 5's PNT_NO is returned (16068).
It doesn't matter what values are returned in 500 PCT_MATCH rows, I can filter them out. I'm sorry if I am over-explaining this, but any help would be greatly appreciated!
| Group | Value | OWNRSHP_ID | PNT_NO | PNT_MATCH | ADDRESS_LINE_1 |
| 2 | 500 | 900 | 11164 | 701 Green | |
| 2 | 1.5 | 70 | 6019302309 | 11164 | 701 Green |
| 2 | 1.1 | 70 | 900237465 | NA | 2000 Green |
| 3 | 500 | 900 | 16068 | 820 Blue | |
| 3 | 0.8 | 70 | 1245868609 | NA | 820 Orange |
| 3 | 0.8 | 70 | 125972567 | 16068 | 820 Blue |
| 3 | 0.8 | 70 | 28686748366 | 16068 | 820 Blue |
<tbody>
</tbody>
Column 5 (PNT_MATCH) is what I want to populate using a formula, in the example I've typed in the values for illustration. I have thousands of lines of data, hence why I can't type in all needed values manually.
Each row of data belongs to a Group (column 1), and the first row of each new group has the value 500 in column 2 (Value). For all non-500 Value rows within each distinct group, I want the cell in column 5 (PNT_MATCH) to return the value from the 500 row's column 4 (PNT_NO), if the address in that given row matches the address in the 500 row. If the address does not match, NA should be returned.
So as illustrated above, the address in Row 3 matches the address in Row 2 (which is a 500 PCT_MATCH row), so the value of Row 2's PNT_NO is returned in Row 3's PNT_MATCH column (11164). The address in Row 4 does not match so NA is returned. Row 5 represents a new Group (Group 3) so the pattern repeats - Row 6's address does not match Row 5's so NA is returned, then Rows 7 and 8's addresses do match Group 3's 500 Value row so the value of Row 5's PNT_NO is returned (16068).
It doesn't matter what values are returned in 500 PCT_MATCH rows, I can filter them out. I'm sorry if I am over-explaining this, but any help would be greatly appreciated!
