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.
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.
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.
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).
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.
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.
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.
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.
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.
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 546 |
| Nodes: | 16 (2 / 14) |
| Uptime: | 07:56:45 |
| Calls: | 10,386 |
| Calls today: | 1 |
| Files: | 14,058 |
| Messages: | 6,416,648 |