You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: Chavista
Chavista Author
User level: Level 1
7 points

Need formula for justifying (matching) two columns in Numbers spreadsheet

Imagine a "master" column with the following data:


one

two

three

four

five


Now imagine a second column that includes this data:


five

three

[empty cell]

one


I'd like to find a way to align the two columns so that all values that occur in both columns will appear side by side. So, for example, if "three" in the master column is on row 16, then three in the second column will be moved to row 16 as well.


If there's a value in the second column that isn't matched in the master column, then that value won't be deleted; it will simply be moved to the bottom of the column, or whatever.


Can anyone tell me formula I can use to do this? Thanks.

MacBook Pro 13", macOS 10.12

Posted on Dec 15, 2019 2:26 PM

Reply
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Posted on Dec 15, 2019 5:55 PM

This should produce the result you need:

Column A contains your 'master list'

Column E contains the 'second' list.

Column B contains the results of a search of Column F for each item listed in column A.


The search is carried out by the formula entered in cell B2, then filled down to the end of column B.


B2: IFERROR(INDEX(E,MATCH(A2,E,0)),"")


In the core formula, shown in bold,

  • MATCH gets the value from 'this row' of column A, searches for that value in column E, and returns a number indicating the position of that value in the list in column E
  • INDEX takes the number returned by MATCH, and returns the value in that position in the list in column E to the cell containing the formula.
  • The 0 in MATCH means accept only an exact match. If a match is not found, MATCH returns an error message.
  • IFERROR is an error trap. If any part of the fomrula returns an error, IFERROR catches it, skips past the formula, and returns a null string (which displayes as a blank cell).


Regards,

Barry

View in context

Similar questions

4 replies
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Dec 15, 2019 5:55 PM in response to Chavista

This should produce the result you need:

Column A contains your 'master list'

Column E contains the 'second' list.

Column B contains the results of a search of Column F for each item listed in column A.


The search is carried out by the formula entered in cell B2, then filled down to the end of column B.


B2: IFERROR(INDEX(E,MATCH(A2,E,0)),"")


In the core formula, shown in bold,

  • MATCH gets the value from 'this row' of column A, searches for that value in column E, and returns a number indicating the position of that value in the list in column E
  • INDEX takes the number returned by MATCH, and returns the value in that position in the list in column E to the cell containing the formula.
  • The 0 in MATCH means accept only an exact match. If a match is not found, MATCH returns an error message.
  • IFERROR is an error trap. If any part of the fomrula returns an error, IFERROR catches it, skips past the formula, and returns a null string (which displayes as a blank cell).


Regards,

Barry

Reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Dec 15, 2019 3:28 PM in response to Chavista


A more detailed description of the data is needed.


Is data in the first (master) column entered directly or calculated? If calculated, what is the formula used in this column?

Are there any gaps ('empty' cells) in the first column data?

Are there any duplicate items in the first column?


Your example includes an empty cell in the second column. Could there be more than one empty cell in this data set?

Values in the second column that do not match any item in the first column are to be moved to the bottom of the list in the second column.


What is to happen in the cases where a value in the first column is not matched by any value in the second set?

What is to happen in the case of 'or whatever'?


Regards,

Barry

Reply
User profile for user: Chavista
Chavista Author
User level: Level 1
7 points

Dec 15, 2019 4:15 PM in response to Chavista

Sorry, I just posted a simple example.


My "real life" project features a column with a few hundred image names, each one unique. For example: states-ca-bird.jpg


The second column features a subset of those names. So if states.ca-bird.jpg happens to be listed in that column, I'd like it aligned with states.ca-bird.jpg in the other column.


There are a number of empty cells, mostly in the second column.


On second thought, it doesn't really matter what happens to values that don't have a match in the other column - just as long as they aren't deleted. The best solution is to simply match every pair of like values and ignore everything else.

Reply

Need formula for justifying (matching) two columns in Numbers spreadsheet

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up