Numbers formula help — Concatenate a List within cells

Question

Level 1

0 points

Numbers formula help — Concatenate a List within cells

Hi All,

Im trying to create a couple of neat formulas (i.e. one that isn't a hundred lines long, and is easily duplicatable) to do the following;

Writing the formula into Sheet 2, Column C, Row 1 : Search all cells in Sheet 1, Column K (text) for the contents of Sheet 2, Column A, Row 1 (text). If this is found, display a list of the corresponding contents of Sheet 1, Column C. See screenshot for my spreadsheet and current formula, which seems to work, but is very long, clunky and not easy to reproduce.
Writing the formula into Sheet 2, Column D, Row 1 : Search all cells in Sheet 1, Column C for the contents of Sheet 2, Column C, Row 1. Based on the corresponding matches, display the lowest corresponding value found in Sheet 1, Column E. See screenshot of the spreadsheet to better understand my request — no formula has yet been attempted.

As you can (hopefully) tell from my spreadsheet, I need to be able to readily replicate these formulas for every row. This is why my current formula isn't really appropriate. If to achieve the desired result I need to change the structure of my spreadsheets, I am willing to do this.

Any help would be MASSIVELY appreciated as this is right at the limits of my current knowledge.

Thanks in advance,

Merlin

iMac, OS X Yosemite (10.10.4)

Posted on Aug 27, 2015 3:41 AM

Reply

Answer 1

Yellowbox

Level 7

20,383 points

Aug 27, 2015 5:09 AM in response to merlinduff

Hi Merlin,

I can't read your first two screen shots. Instead of command shift 3 (whole screen) use command shift 4 and drag over the relevant (small) part of a table.

In the third screen shot, we can't see the column and row labels.

For greater clarity, show the Table names (Format Panel > Table > Tick Table Name).

Regards,

Ian.

Reply

Answer 2

merlinduff Author

Level 1

0 points

Aug 27, 2015 6:00 AM in response to Yellowbox

Hi Ian,

Thanks for your reply.

If you click on the above images they enlarge, but I've also attached close up screenshots of the top few rows of the sheets.

Thanks,

M.

Reply

Answer 3

t quinn

Level 5

7,043 points

Aug 27, 2015 7:05 AM in response to merlinduff

Hi merlin,

Your description of your problem is confusing to me.

In Numbers a sheet can have many tables and objects on its canvas. I think you are saying "sheet" when you should be describing a table.

Communications::A1 describes the upper left cell in the table communications. Using these designations will make your communications clearer.

So when you say 'Sheet 1' you mean the table "Process steps"?

What table is "Sheet 2"? "communications"?

Could you rewrite your questions so they would be a little clearer to those of us steeped in our Numbers conventions? It is a lot of work simply translating your question.

quinn

Reply

Answer 4

Yellowbox

Level 7

20,383 points

Aug 27, 2015 7:19 AM in response to t quinn

Hi Quinn,

Just taking another look at Merlin's original screen shots (and squinting my eyes), the tables are on different Sheets

Good point though 🙂.

Regards,

Ian.

Reply

Answer 5

t quinn

Level 5

7,043 points

Aug 27, 2015 7:20 AM in response to Yellowbox

Hi Ian,

I am sure they are, it just doesn't help clarify the question.

quinn

Reply

Answer 6

merlinduff Author

Level 1

0 points

Aug 27, 2015 7:35 AM in response to t quinn

Hi Ian and Quinn,

Apologies for any confusion stemming from nomenclature.

Rewritten as per your request Quinn my questions read;

Writing the formula into 'Communications', Column C, Row 1 : Search all cells in 'Process Steps', Column K for the contents of 'Communications', Column A, Row 1 (text). If this is found, display a list of the corresponding contents of 'Process Steps', Column C. See screenshot for my spreadsheet and current formula, which seems to work, but is very long, clunky and not easy to reproduce.
Writing the formula into 'Communications', Column D, Row 1 : Search all cells in 'Process Steps', Column C for the contents of 'Process Steps', Column C, Row 1. Based on the corresponding matches, display the lowest corresponding value found in 'Process Steps', Column E. See screenshot of the spreadsheet to better understand my request — no formula has yet been attempted.

Any clearer for you? I'm used to talking in layman's terms around Numbers but am trying my best to make my request easier to understand for you guys.

Thanks,

M.

Reply

Answer 7

Yellowbox

Level 7

20,383 points

Aug 27, 2015 8:01 AM in response to merlinduff

Hi Merlin,

Your screen shots are better, but still not easy to read.

Question 1

Writing the formula into 'Communications', Column C, Row 1 : Search all cells in 'Process Steps', Column K for the contents of 'Communications', Column A, Row 1 (text). If this is found, display a list of the corresponding contents of 'Process Steps', Column C. See screenshot for my spreadsheet and current formula, which seems to work, but is very long, clunky and not easy to reproduce.

You want the formula in Communications C (the yellow-green column in my screen shot)?

Are we getting close?

Will continue tomorrow.

Regards,

Ian.

Reply

Answer 8

Yellowbox

Level 7

20,383 points

Aug 28, 2015 1:34 AM in response to Yellowbox

Hi Merlin,

Question 1.

I can't find a way to shorten the formula. It is using FIND to search a list that is already concatenated (Process Steps::K) to produce another concatenated list in each row of Communications::C

Will that formula fill down in Communications::C?

Question 2.

Sorry, I have no clue.

Regards,

Ian.

Reply

Answer 9

Yellowbox

Level 7

20,383 points

Aug 28, 2015 3:36 AM in response to merlinduff

Hi Merlin,

We are halfway there.

I have taken up your offer:

If to achieve the desired result I need to change the structure of my spreadsheets, I am willing to do this.

Add another Table to "deconcatenate" Column K in "Process Steps". This Table "Step Codes affected by Communication Codes" is a small subset of codes to illustrate the formula.