hurtlingturtle
New Member
- Joined
- Jan 28, 2019
- Messages
- 2
Hi All,
I hope someone here can help me out. I've been searching everywhere and cannot find what I need.
To explain, I am working on a workbook that has a table within it that contains load of rows of data. This table is called TeamSheetsAll. Within that table are many columns, but relevant here are the columns [Fiscal period] and [over 1m]. Fiscal period is a text value whereas over 1m is a boolean TRUE/FALSE value. The other field that is important is the [Op ID] which is a text value to be returned.
I should also state that the A6 references are used to check an increasing list of data in my new table to ensure I do not duplicate rows.
I am trying to generate a unique list of op ids that meet the specific logical requirements of my IF statement. The formula I currently have is as follows. But this results in empty data.
=IFERROR(INDEX(IF(AND(TeamSheetsAll[Fiscal period]=CurrentFQ,TeamSheetsAll[over 1m]=TRUE),TeamSheetsAll[Op ID],""), MATCH(0,COUNTIF($A$6:A6, IF(AND(TeamSheetsAll[Fiscal period]=CurrentFQ,TeamSheetsAll[over 1m]=TRUE),TeamSheetsAll[Op ID],"")), 0)),"")
However, if I do this without the AND logic then the following formula works.
=IFERROR(INDEX(IF(TeamSheetsAll[over 1m]=TRUE,TeamSheetsAll[Cloud Op ID],""), MATCH(0,COUNTIF($A$6:A6, IF(TeamSheetsAll[over 1m]=TRUE,TeamSheetsAll[Cloud Op ID],"")), 0)),"")
Similarly if I replace this with OR logic it works also. AND does not. Just to be clear I have triple checked the data and I absolutely do have data records in the table that meet the AND criteria
Any advice is most welcome.
cheers
Bruce
I hope someone here can help me out. I've been searching everywhere and cannot find what I need.
To explain, I am working on a workbook that has a table within it that contains load of rows of data. This table is called TeamSheetsAll. Within that table are many columns, but relevant here are the columns [Fiscal period] and [over 1m]. Fiscal period is a text value whereas over 1m is a boolean TRUE/FALSE value. The other field that is important is the [Op ID] which is a text value to be returned.
I should also state that the A6 references are used to check an increasing list of data in my new table to ensure I do not duplicate rows.
I am trying to generate a unique list of op ids that meet the specific logical requirements of my IF statement. The formula I currently have is as follows. But this results in empty data.
=IFERROR(INDEX(IF(AND(TeamSheetsAll[Fiscal period]=CurrentFQ,TeamSheetsAll[over 1m]=TRUE),TeamSheetsAll[Op ID],""), MATCH(0,COUNTIF($A$6:A6, IF(AND(TeamSheetsAll[Fiscal period]=CurrentFQ,TeamSheetsAll[over 1m]=TRUE),TeamSheetsAll[Op ID],"")), 0)),"")
However, if I do this without the AND logic then the following formula works.
=IFERROR(INDEX(IF(TeamSheetsAll[over 1m]=TRUE,TeamSheetsAll[Cloud Op ID],""), MATCH(0,COUNTIF($A$6:A6, IF(TeamSheetsAll[over 1m]=TRUE,TeamSheetsAll[Cloud Op ID],"")), 0)),"")
Similarly if I replace this with OR logic it works also. AND does not. Just to be clear I have triple checked the data and I absolutely do have data records in the table that meet the AND criteria
Any advice is most welcome.
cheers
Bruce