I have a problem that I cannot solve in Numbers. Maybe I haven't hit on the
right function or don't know how to use it properly.

I have a Numbers file which contains worksheet W1 and worksheet W2.
Table T1 is in W1, table T2 in W2.
Is the text string in, say, C16 in T1 to be found anywhere in column D of T2,
True or False?

T1/C and T2/D are both formatted as Text, but are not of equal length. Both
are too big for visual inspection.

I'd be glad of any suggestions.

John.

--
You're not an old dog until you can't learn new tricks.

On 01/05/2023 10:56, John Hill wrote:
> I have a problem that I cannot solve in Numbers. Maybe I haven't hit on the
> right function or don't know how to use it properly.
>
> I have a Numbers file which contains worksheet W1 and worksheet W2.
> Table T1 is in W1, table T2 in W2.
> Is the text string in, say, C16 in T1 to be found anywhere in column D of T2,
> True or False?
>
> T1/C and T2/D are both formatted as Text, but are not of equal length. Both
> are too big for visual inspection.
>
> I'd be glad of any suggestions.

You could write a formula using one of the MATCH/XMATCH/COUNTMATCHES
functions.

--
Chris

On 1 May 2023 at 11:43:00 BST, "Chris Ridd" <chrisridd@mac.com> wrote:

> On 01/05/2023 10:56, John Hill wrote:
>> I have a problem that I cannot solve in Numbers. Maybe I haven't hit on the
>> right function or don't know how to use it properly.
>>
>> I have a Numbers file which contains worksheet W1 and worksheet W2.
>> Table T1 is in W1, table T2 in W2.
>> Is the text string in, say, C16 in T1 to be found anywhere in column D of T2,
>> True or False?
>>
>> T1/C and T2/D are both formatted as Text, but are not of equal length. Both
>> are too big for visual inspection.
>>
>> I'd be glad of any suggestions.
>
> You could write a formula using one of the MATCH/XMATCH/COUNTMATCHES
> functions.

Thank you - I'll work on that.

I find I've forgotten nearly everything I knew about formulae and functions!
And one of the tables need attention (imported from another application).

Back to it tomorrow.

John.
--
An infinitely complex system can fail in an infinite number of ways.

John Hill wrote:
> On 1 May 2023 at 11:43:00 BST, "Chris Ridd" <chrisridd@mac.com> wrote:
>
>> On 01/05/2023 10:56, John Hill wrote:
>>> I have a problem that I cannot solve in Numbers. Maybe I haven't hit on the
>>> right function or don't know how to use it properly.
>>>
>>> I have a Numbers file which contains worksheet W1 and worksheet W2.
>>> Table T1 is in W1, table T2 in W2.
>>> Is the text string in, say, C16 in T1 to be found anywhere in column D of T2,
>>> True or False?
>>>
>>> T1/C and T2/D are both formatted as Text, but are not of equal length. Both
>>> are too big for visual inspection.
>>>
>>> I'd be glad of any suggestions.
>>
>> You could write a formula using one of the MATCH/XMATCH/COUNTMATCHES
>> functions.
>
> Thank you - I'll work on that.
>
> I find I've forgotten nearly everything I knew about formulae and functions!
> And one of the tables need attention (imported from another application).
>
> Back to it tomorrow.

I don't know about Numbers, but in Excel there is a "Find" function

Capture the text string from C16 in T1, paste it into the "Find" field.

Select column D of T2 and apply the "Find" function. If the string is
present, it will be found. Indeed if there are several instances of the
string, or a longer string containing the text in the "Find" argument,
those could also be found, depending on the "Match" setting.

More difficult if you want to automate it - as in "list all instances
where a string in T1 is present in column D of (or anywhere in) T2".

Probably we need to know more about the tables and why you want to do this.

--
Graham J

On 01/05/2023 15:26, John Hill wrote:
> On 1 May 2023 at 11:43:00 BST, "Chris Ridd" <chrisridd@mac.com> wrote:
>
>> On 01/05/2023 10:56, John Hill wrote:
>>> I have a problem that I cannot solve in Numbers. Maybe I haven't hit on the
>>> right function or don't know how to use it properly.
>>>
>>> I have a Numbers file which contains worksheet W1 and worksheet W2.
>>> Table T1 is in W1, table T2 in W2.
>>> Is the text string in, say, C16 in T1 to be found anywhere in column D of T2,
>>> True or False?
>>>
>>> T1/C and T2/D are both formatted as Text, but are not of equal length. Both
>>> are too big for visual inspection.
>>>
>>> I'd be glad of any suggestions.
>>
>> You could write a formula using one of the MATCH/XMATCH/COUNTMATCHES
>> functions.
>
> Thank you - I'll work on that.
>
> I find I've forgotten nearly everything I knew about formulae and functions!
> And one of the tables need attention (imported from another application).

If it helps, the function syntax looks very similar to Excel's (for
better or worse) so googling for Excel functions will usually bring up
something that's also applicable to Numbers.

--
Chris

On 1 May 2023 at 15:52:02 BST, "Graham J" <nobody@nowhere.co.uk> wrote:

> John Hill wrote:
>> On 1 May 2023 at 11:43:00 BST, "Chris Ridd" <chrisridd@mac.com> wrote:
>>
>>> On 01/05/2023 10:56, John Hill wrote:
>>>> I have a problem that I cannot solve in Numbers. Maybe I haven't hit on the
>>>> right function or don't know how to use it properly.
>>>>
>>>> I have a Numbers file which contains worksheet W1 and worksheet W2.
>>>> Table T1 is in W1, table T2 in W2.
>>>> Is the text string in, say, C16 in T1 to be found anywhere in column D of T2,
>>>> True or False?
>>>>
>>>> T1/C and T2/D are both formatted as Text, but are not of equal length. Both
>>>> are too big for visual inspection.
>>>>
>>>> I'd be glad of any suggestions.
>>>
>>> You could write a formula using one of the MATCH/XMATCH/COUNTMATCHES
>>> functions.
>>
>> Thank you - I'll work on that.
>>
>> I find I've forgotten nearly everything I knew about formulae and functions!
>> And one of the tables need attention (imported from another application).
>>
>> Back to it tomorrow.
>
>
> I don't know about Numbers, but in Excel there is a "Find" function
>
> Capture the text string from C16 in T1, paste it into the "Find" field.
>
> Select column D of T2 and apply the "Find" function. If the string is
> present, it will be found. Indeed if there are several instances of the
> string, or a longer string containing the text in the "Find" argument,
> those could also be found, depending on the "Match" setting.
>
> More difficult if you want to automate it - as in "list all instances
> where a string in T1 is present in column D of (or anywhere in) T2".
>
> Probably we need to know more about the tables and why you want to do this.

Table T1 (which contains a number of columns) has been exported as a csv file
to another application, which has modified it. The data was then exported from
the other application, again as a csv file. I wanted to know what had been
lost on the way (if anything) or changed.

John.
--
Classic computing: Computers do what you tell them to do, not what you want them to do.
Modern computing: Computers do what they want to do, no matter what you tell them to do.

On 01/05/2023 18:44, John Hill wrote:
> Table T1 (which contains a number of columns) has been exported as a csv file
> to another application, which has modified it. The data was then exported from
> the other application, again as a csv file. I wanted to know what had been
> lost on the way (if anything) or changed.

Comparing two CSV files is bread and butter Unix command line stuff.

Start a Terminal session, change directory to where the two files are
and use the diff command, viz:

$ diff -s original.csv modified.csv

(The -s flag explicitly confirms if the two files are the same.)

The output from diff is a bit esoteric and getting it to appear just the
way you want can be a bit tricky so it might be simplest to use diff
first and having confirmed that there is a difference then use Numbers.

One, relatively simple approach in Numbers is to use 'Conditional
highlighting' to highlight the cell or row that differs from its
equivalent in the other table.

In Numbers...

- Go to W2 and select cell A1 in T2.
- Choose Menu->Format->Conditional Highlighting...
- Click "Add a rule"
- Choose "123 Number" on the left if not already highlighted
- Choose "Not equal to"
- In the cell address box type in "T1::A1" (Tip: you can drag the
address box off the sheet so that you don't lose it when you switch to
tab W1 and then just click on A1 to fill in the address.)
- In the style box choose "Red Fill"
- Click "Done"

Now, whenever A1 differs between tables, the A1 cell in table 2 will
have a red background.

To copy the highlight rule to all the cells...
- select cell A1 in table T2
- Menu->Format->Copy Style
- select all cells in table T2
- Menu->Format->Paste Style

and if all has worked then the differing cells will show up.

Obviously(!) if a whole row has been added or removed then all
subsequent rows will show as changed so you might have to insert a dummy
row in the appropriate table to compensate.

--
Bruce Horrocks
Surrey, England

Bruce Horrocks <07.013@scorecrow.com> wrote:
> On 01/05/2023 18:44, John Hill wrote:
>> Table T1 (which contains a number of columns) has been exported as a csv file
>> to another application, which has modified it. The data was then exported from
>> the other application, again as a csv file. I wanted to know what had been
>> lost on the way (if anything) or changed.
>
> Comparing two CSV files is bread and butter Unix command line stuff.
>
> Start a Terminal session, change directory to where the two files are
> and use the diff command, viz:
>
> $ diff -s original.csv modified.csv
>
> (The -s flag explicitly confirms if the two files are the same.)
>
> The output from diff is a bit esoteric and getting it to appear just the
> way you want can be a bit tricky so it might be simplest to use diff
> first and having confirmed that there is a difference then use Numbers.
>
> One, relatively simple approach in Numbers is to use 'Conditional
> highlighting' to highlight the cell or row that differs from its
> equivalent in the other table.
>
> In Numbers...
>
> - Go to W2 and select cell A1 in T2.
> - Choose Menu->Format->Conditional Highlighting...
> - Click "Add a rule"
> - Choose "123 Number" on the left if not already highlighted
> - Choose "Not equal to"
> - In the cell address box type in "T1::A1" (Tip: you can drag the
> address box off the sheet so that you don't lose it when you switch to
> tab W1 and then just click on A1 to fill in the address.)
> - In the style box choose "Red Fill"
> - Click "Done"
>
> Now, whenever A1 differs between tables, the A1 cell in table 2 will
> have a red background.
>
> To copy the highlight rule to all the cells...
> - select cell A1 in table T2
> - Menu->Format->Copy Style
> - select all cells in table T2
> - Menu->Format->Paste Style
>
> and if all has worked then the differing cells will show up.
>
> Obviously(!) if a whole row has been added or removed then all
> subsequent rows will show as changed so you might have to insert a dummy
> row in the appropriate table to compensate.
>

In interesting suggestion. But unfortunately there is no guarantee that the
sequence of entries has been preserved between T1 and T2. Hence the need to
look in the whole column in T2 for a match with the content of an
individual cell in T1.

John.

--
The Moving Finger writes; and, having writ,
Moves on: nor all thy Piety nor Wit
Shall lure it back to cancel half a Line,
Nor all thy Tears wash out a Word of it.

John Hill wrote:

[snip]

>>
>
> In interesting suggestion. But unfortunately there is no guarantee that the
> sequence of entries has been preserved between T1 and T2. Hence the need to
> look in the whole column in T2 for a match with the content of an
> individual cell in T1.

Is there a column in ascending sequence?

If not create one. Then you can sort the whole spreadsheet by this
column and guarantee to get it back to its initial state.

Now sort by the column containing the text you are interested in, for
both the original and potentially modified files. If the files match in
these columns the text strings are unaltered. If not, the previous
suggestion should identify the changed strings.

Another idea - the external process which potentally modifies the text
strings - can you get this process to write a log file of the changes it
has made?

--
Graham J

On 02/05/2023 09:38, John Hill wrote:
> In interesting suggestion. But unfortunately there is no guarantee that the
> sequence of entries has been preserved between T1 and T2. Hence the need to
> look in the whole column in T2 for a match with the content of an
> individual cell in T1.

The usual trick in that case is to sort both CSV files, and compare the
sorted versions.

sort file1.csv > file1-sorted.csv
sort file2.csv > file2-sorted.csv
diff -bu file1-sorted.csv file2-sorted.csv

The sort method doesn't matter much, as long as you sort the same way on
both files.

--
Chris

On 1 May 2023 at 10:56:57 BST, "John Hill" <watcombeman@yahoo.co.uk> wrote:

> I have a problem that I cannot solve in Numbers. Maybe I haven't hit on the
> right function or don't know how to use it properly.
>
> I have a Numbers file which contains worksheet W1 and worksheet W2.
> Table T1 is in W1, table T2 in W2.
> Is the text string in, say, C16 in T1 to be found anywhere in column D of T2,
> True or False?
>
> T1/C and T2/D are both formatted as Text, but are not of equal length. Both
> are too big for visual inspection.
>
> I'd be glad of any suggestions.
>
> John.

I have now achieved my aim using ISERROR on the results of MATCH.

Now to examine the results!

Thank you all very much. I have learned a lot in the process, recovering old
skills (and I'm talking fifteen years old).

John.
--
God made the integers; all else is the work of man.

On 02/05/2023 09:38, John Hill wrote:
> In interesting suggestion. But unfortunately there is no guarantee that the
> sequence of entries has been preserved between T1 and T2. Hence the need to
> look in the whole column in T2 for a match with the content of an
> individual cell in T1.

In that case be wary of duplicates in the original where one is removed
from the modified file.

e.g. if you had a price column and two items costing £5.99 then the
MATCH command will find the same £5.99 entry for both rows, think that
all is well and not warn you that one row was removed.

--
Bruce Horrocks
Surrey, England