Compare two columns for the same number

Hi John,

Please post a screen shot of what your subjects table looks like. You can show us some made-up data. Please include the column and row labels so that we can see the big picture (but a small example table is all that is necessary).

Also, a screen shot of an example of your co-worker's table will add to our understanding of the overall aim.

Regards,

Ian.

HI John,

Much clearer than your original description! Thank you.

You wrote: "'…then output the results in the column "Chosen one"…'

Do you want the first result in the example to be

"Mary Doe"

or

"Mary Doe, Streetname 4 12345, Shrewsbury"

I've opted for the second, as can be seen below.

Copy/Pasteable version:

Entered in H2, then filled down as many rows as there are numbers in the list of random numbers:

OFFSET(A$1,G2,1)&" "&OFFSET(A$1,G2,2)&", "&OFFSET(A$1,G2,3)&" "&OFFSET(A$1,G2,4)&", "&OFFSET(A$1,G2,5)

Stacked to show structure:

OFFSET(A$1,G2,1)&" "&

OFFSET(A$1,G2,2)&", "&

OFFSET(A$1,G2,3)&" "&

OFFSET(A$1,G2,4)&", "&

OFFSET(A$1,G2,5)

Regards,

Barry

John,

the base of the OFFSET function needs to be a reference.

When you specify the base of the OFFSET function, click on the cell A1 for getting the reference to this cell, and then adjust the referenced cell for making the row absolute, rather than just keying "A$1".

Paul.

Hi Dohn ;-),

The formula shown first in your post above looks correct, and the fact that the cell references are in 'lozenges' shows that Numbers also thinks so at first glance.

I have no idea why the A$1 references all change to 1s. suspect I'd need to have been looking over your shoulder to even have a clue to what happened there.

The error message tells you OFFSET needs a cell reference in that position to establish the base cell (A1) from which the offsets down and right are counted. For the first 'first name', OFFSET is to look 4 rows down from and one column to the right of A1.

The $ in front of 1 makes A$1 continue to place the base cell at A1 as the formula is filled down the column.

"It would be just great…" is an option.

Here are two formulas.

I've hidden column H to avoid distraction, and put the formulas in columns I and J. Either formula can be entered in H2 and filled down to give the same results in column H.

The first puts the number from column A in the same row of column I, IF that number is in the list of random numbers.

The second places whatever text you place between the quotation marks into each row of column J that has one of the listed random numbers in column A.

Both place a null string (a text value with zero length) into cells on rows that do not have one of the random numbers in column A.

I2: IF(COUNTIF(G,A2)<1,"",A2)

J2: IF(COUNTIF(G,A2)<1,"","X")

As before, replace the three commas in the formula with semi colons to fit your region.

Regards,

Barry

Hi Paul,

Actually, I think John copied the full formula from my post above and pasted it into the formula editor (see the first image in his post).

How it lost the A$ part of the cell reference (in every instance of OFFSET) is a mystery.

Regards,

Barry

Hi John,

I checked the image of your formula against the one in my table, and found them to be identical.

Then I glanced at the Town column, and the light went on.

I suspect that in your region you would write a number including a decimal fraction part like this:

4,25

as opposed to the style used here:

4.25

If that's the case, this revised version should work in your table:

current: OFFSET(A$1,G2,1)&" "&OFFSET(A$1,G2,2)&", "&OFFSET(A$1,G2,3)&" "&OFFSET(A$1,G2,4)&", "&OFFSET(A$1,G2,5)

revised: OFFSET(A$1;G2;1)&" “&OFFSET(A$1;G2;2)&", “&OFFSET(A$1;G2;3)&" “&OFFSET(A$1;G2;4)&", "&OFFSET(A$1;G2;5)

In regions where the comma is used as the decimal separator, formulas use the semi colon as the list separator.

Regarding sorting:

Numbers always sorts the rows, and bases the sort on the values in a specified column.

The full line data will be listed in column G in the order set by the placing of the numbers in column G. Placing 13 in row 2 and 4 in row 6 would reverse the positions of Mary and Eric in the list in column H.

To sort only the rows containing the selected subjects, you';; need to either keep the table sorted in the order you want (likely by column B—surnames, and use a formula ( =ROW()-1) to number the rows in column A. Data retrieved in column H will then be automatically retrieved in order, sorted by surname.

" would it be possible to retain all the columns somehow?"

To extract the data into separate columns requires only having one column for each piece of the data you want to extract, then placing the part of the formula dealing with that piece into each column.

Put the first two OFFSETs into one column, joined by &" "& to get the first and last names in that column.

This will give you the full name for use on a DYMO label or in the greeting line of a letter.

Placed on a separate table, the cell references in the formulas will have to include the name of the data table.Numbers will automatically add these if you place the formula(s) as specified in columns of the existing table, then, after they are working as needed, selecting columns G and H (and any added when splitting the formula to place parts of the required data into separate columns), and pulling those columns off the existing table to form a new table.

Steps to do that:

• Select the tables to form the new table by clicking on the first column (G) then dragging right to expand the selection to include all columns moving to the new table.
• Release the mouse button, ten click and hold on one if the selected column reference tabs.
• After a slight pause, the selected columns will 'rise' from the canvas, and may be dragged right.
• When they have been dragged more than a column width from the rest of the original table, you'll see a slight jerk as they are separated. Release the mouse button to drop the new table onto the canvas, then click the bullseye off the top left of the new table and drag it to where you want it.

Using mail merge to get this date onto an envelope or into a Microsoft Word letter will require transferring the data to an MS Excel file, using copy and paste,

Regards,

Barry

Hi John,

Take a look at the Streetname list in your New code table. The number at the end of the address should match the random number beside it.

Mary's street end in 4 because the OFFSET getting her name looks in the 4th row below the base A1.

When you fill the formula down from H2 into H3 though, the lack of "$", removed from A$1, tells Numbers to increment the base reference to A2.

So in that cell, OFFSET gets the data from the 6th row below A2

Filled down to the row containing 19 in the random number list, OFFSET is looking for the values in the 19th row below A6,

This causes the "invalid reference" error because row 21 is the last row in the table, and OFFSET's reference is to row 25.

To fix: Double click on H2 to open the Formula Editor.

Change each of the A1 references to A$1

(easiest way to do this is to click the triangle on the lozenge then hec the Preserve Row box)

Then fill the formula down to the end of the list of random numbers.

Regards,

Barry

PS: No worries on the Barry/Garry mixup. My mom did that often. (I have a brother named Gary.)

Being simple-minded, I would suggest a slightly different (I think simpler) approach to this that takes advantage of the ability to work easily with multiple tables on a Numbers sheet, and also avoids the "volatile" OFFSET function.

The first table is like your original table, but in "typical Numbers style" (where one can easily have multiple tables on one sheet) I've moved the randomized number picks to the first column of a 'Random Picks' table, which has the same columns as the 'Address Data' table plus and extra column for the address for the label.

Set up this way, only two short formulas are needed:

In B2 filled right to F2 and down:

=INDEX(Address Data::B,$A2+1)

In G2, filled down:

=B2&" "&C2&", "&D2& " "&E2&", "&F2

In regions where , is a decimal separator, these formulas would be:

=INDEX(Address Data::B;$A2+1)

=B2&" "&C2&", "&D2& " "&E2&", "&F2 (unchanged from above)

The values in the 'For Label' column can then be copy-pasted wherever needed.

SG