+ Reply to Thread
Results 1 to 16 of 16

Cross Referencing

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Cross Referencing

    OK guys...

    I have a table that has a list of herbs and spices, and the foods that they are commonly used to season. This is the master table that I values into.

    Here's an example

    Herb/Spice Complements
    Oregano Chicken, Potatoes
    Curry Chicken, Rice
    Onion Beef, Pork, Chicken, Potatoes
    Garlic Chicken, Potatoes, Tomatoes
    Pepper Beef, Potatoes

    This isn't the whole table, just an sample.

    What I want to do, is on a separate sheet have a table that shows the reverse:

    Food Suggested Seasoning
    Chicken Oregano, Curry, Onion, Garlic
    Beef Onion, Pepper
    Pork Onion
    Potatoes Onion, Pepper
    Rice Curry
    Tomatoes Garlic

    I want this table to pull from the first one so that I only have to update the primary table.

    I figure this will involve un-concatenating the lists from the first table (which I can do, I can also re-concatenate them into the second column list on the secondary table)
    I just started getting an enormously complex formula, so I figured I would ask if there was an easier way.

    Thanks, everybody!
    Last edited by Yakov on Excel; 05-09-2016 at 06:03 PM. Reason: Typo

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Cross Referencing

    I would clean the data up then just use pivot tables to make things simple for you.

    Aggregating and slicing data IS exactly what pivot tables were made for.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Cross Referencing

    OK, so if I parse all the lists out in table1 so that they have their own separate cells, what would the proper setup in a pivot table be for creating that secondary table?

  4. #4
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Cross Referencing

    Quote Originally Posted by Yakov on Excel View Post
    OK, so if I parse all the lists out in table1 so that they have their own separate cells, what would the proper setup in a pivot table be for creating that secondary table?
    Because I can't figure it out.

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Cross Referencing

    Sure take a look at this example I attached.
    Attached Files Attached Files

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cross Referencing

    Are you open to using a VBA function to do this?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50
    Quote Originally Posted by Tony Valko View Post
    Are you open to using a VBA function to do this?
    Yep, VBA is great

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cross Referencing

    Try this...

    Copy the VBA code at the link below and paste it into a general module.

    https://www.excelforum.com/showthread.php?p=3096647

    Then...

    Data Range
    A
    B
    1
    Herb/Spice
    Complements
    2
    Oregano
    Chicken, Potatoes
    3
    Curry
    Chicken, Rice
    4
    Onion
    Beef, Pork, Chicken, Potatoes
    5
    Garlic
    Chicken, Potatoes, Tomatoes
    6
    Pepper
    Beef, Potatoes
    7
    8
    9
    10
    Food
    Suggested Seasoning
    11
    Chicken
    Oregano, Curry, Onion, Garlic
    12
    Beef
    Onion, Pepper
    13
    Pork
    Onion
    14
    Potatoes
    Oregano, Onion, Garlic, Pepper
    15
    Rice
    Curry
    16
    Tomatoes
    Garlic


    This array formula** entered in B11 and copied down:

    =concatall(IF(ISNUMBER(SEARCH(A11,B$2:B$6)),A$2:A$6,""),", ")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  9. #9
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Cross Referencing

    Quote Originally Posted by Tony Valko View Post
    Try this...

    Copy the VBA code at the link below and paste it into a general module.

    https://www.excelforum.com/showthread.php?p=3096647

    Then...

    Data Range
    A
    B
    1
    Herb/Spice
    Complements
    2
    Oregano
    Chicken, Potatoes
    3
    Curry
    Chicken, Rice
    4
    Onion
    Beef, Pork, Chicken, Potatoes
    5
    Garlic
    Chicken, Potatoes, Tomatoes
    6
    Pepper
    Beef, Potatoes
    7
    8
    9
    10
    Food
    Suggested Seasoning
    11
    Chicken
    Oregano, Curry, Onion, Garlic
    12
    Beef
    Onion, Pepper
    13
    Pork
    Onion
    14
    Potatoes
    Oregano, Onion, Garlic, Pepper
    15
    Rice
    Curry
    16
    Tomatoes
    Garlic


    This array formula** entered in B11 and copied down:

    =concatall(IF(ISNUMBER(SEARCH(A11,B$2:B$6)),A$2:A$6,""),", ")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Marvelous!!! Thanks Tony!

  10. #10
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Cross Referencing

    OK, Tony, this worked initially, but after closing and re-opening the workbook I'm getting a #NAME? error, any ideas?

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cross Referencing

    Did you put the code in the correct place?

    With your file open...

    ALT F11 to open the VBE
    Goto the menu Insert>Module
    Paste the code into the window on the right
    ALT Q to close the VBE and return to Excel

    You'll have to save the file as a macro enabled file in the *.xlsm file format.

  12. #12
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Cross Referencing

    Yep, it's the only module in the project, and it worked fine until I closed and opened the workbook.

    I've attached my file. Let me know what you think.
    Attached Files Attached Files

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cross Referencing

    Worked OK for me.

    On the BySpice sheet I added Lamb to Mint (row 15).

    On the ByDish sheet Mint was added to Lamb (row 7).

    Saved the file then reopened and everything still worked as expected.

  14. #14
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Cross Referencing

    Oy... Rookie fail.... I forgot to enable macros.... am I disowned from the forum?

  15. #15
    Registered User
    Join Date
    02-06-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2016
    Posts
    50

    Re: Cross Referencing

    Thanks for your help guys, if you are interested, I will post the completed database when it is finished so you can snag a copy.

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Cross Referencing

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Cross Referencing -
    By Amolvijay in forum Excel General
    Replies: 2
    Last Post: 08-03-2013, 11:01 PM
  2. Cross Referencing
    By invisiblevoices in forum Excel General
    Replies: 2
    Last Post: 07-29-2010, 02:21 AM
  3. VBA cross referencing
    By 4CB2F in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2009, 06:02 AM
  4. [SOLVED] cross referencing
    By Phil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  5. cross referencing
    By Phil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  6. [SOLVED] cross referencing
    By Phil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. cross referencing
    By Phil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  8. cross referencing
    By Phil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1