Have 1 cell populate another

Terrapin6908

well, for this exercise, they will all have some variation with ABC in the field - so they may be...

1001ABC

1002ABC...etc.

So, for the Route / WO column in SHEET B, it'll only be populated with entries (from SHEET A) that do not have the ABC anywhere in the field - make sense? I am SO sorry for not being able to verbalize this well

Genevieve P.

Alright, so there is a way we can search and see if the returned contact contains certain values (such as ABC), but I think what would be easiest to set up and to explain would be if you had a helper column in Sheet A.

This column can be any type of column, but for our instances let's say it's a checkbox column.

We could put a formula in Sheet A's checkbox column that looks to see if the Route / WO column contains "ABC" or any other text you want excluded. If it does, we'll leave the box empty. However if it doesn't contain this content, we can check the box to indicate that this row is OK to pull through!

Then, all you need to do is add in that additional {column} and criteria to your INDEX(COLLECT formula. You won't need to use my example with the larger IF statement because the IF statement is on Sheet A, doing the initial work for you.

Example formula in the Helper Column:

=IF(CONTAINS("ABC", [Route / WO]@row), 0, 1)

The 1 will check the box. Then we need to search for it in our formula in Sheet B...

Example update:

=IFERROR(INDEX(COLLECT({Route / WO from SHEET A}, {Venue ID Column in SHEET A}, [Venue ID]@row, {Kiosk ID Column from SHEET A}, [Kiosk ID]@row, {Helper Checkbox Column SHEET A}, 1), 1), "No Match")

All I did to update this INDEX(MATCH formula was add in the additional column to look into:

{Helper Checkbox Column SHEET A}

and add in the additional criteria after it, that the checkbox is checked:

1

Does this make sense?

Terrapin6908

I will take a look and try it in just a bit - I will report back

Terrapin6908

So, I created a helper column in SHEET A and input this formula into the first cell:

=IF(CONTAINS("RBX", [Route / WO]@row), 0, 1)

and it checked the box.

In SHEET B, I put this formula into the first cell:

=IFERROR(INDEX(COLLECT({Route / WO from SHEET A}, {Venue ID Column in SHEET A}, [Venue ID]@row, {Kiosk ID Column from SHEET A}, [Kiosk ID]@row, {Helper Checkbox Column SHEET A}, 1), 1), "No Match")

Thus, my result in SHEET B's first cell is NO MATCH

I can only assume this is working?

Genevieve P.

Hi @Terrapin6908

Sounds like it's working, as long as there isn't a match for the row where it says "No Match". I would advise checking this against data you can confirm is in both sheets and you want to pull across.

You may want to change the language at the end of the formula. You can have it say "Blocked Information" or "No Match or Private Data". Something that shows either there's no match, or it's a value you don't want to show.

To update this, just change the words at the end "in these". The formula will return that text if it doesn't find a match.

Cheers!

Genevieve

John Drugan

@Terrapin6908 One issue is you will have to remove extraneous curly brackets. It's hard to see in some of the screenshots but there are some parentheses and curly brackets together like ({ instead of {{.

Note that the double curly brackets are, however, useful in things like adding column contents into email templates, but that is another topic.

I haven't tried other aspects of your formula but it may just be that minor issue. Good luck.