+ Reply to Thread
Results 1 to 8 of 8

Populating columns for data that "refers to" other data, and is "referred by" other data.

  1. #1
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    4

    Populating columns for data that "refers to" other data, and is "referred by" other data.

    Hi,

    I have a set of data strings (in column A). Each data string may refer to another data string, where all referenced data strings are compiled in a cell beside it in column B.

    Now I already have a "Refers To" column for each data string and I want to populate a third column "Referred By". This "Referred By" column should compile all the data strings that refers to the designated data string.

    For example:

    STRING_1 and STRING_2 "Refers To" STRING_3, so therefore STRING_3 is "Referred By" STRING_1 and STRING_2.

    I'm trying to find a way to automate this such that if anything in the "Refers To" column changes, the "Referred By" column updates correspondingly. I'm also trying to have it such that if data has multiple references, that all of the info is put into a single cell.

    An example of the desired output is in the attachments.

    Any suggestions would be really helpful. Sorry if the description is difficult to understand.

    Thank you!
    Attached Images Attached Images
    Last edited by thho; 03-26-2014 at 04:09 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Populating columns for data that "refers to" other data, and is "referred by" other da

    Hi thho

    Welcome to the forum.

    "Refers to/by" is not an Excel expression that I recognise. Perhaps you could attach a spreadsheet (all sensitive data removed) showing exactly what the referral is?

    Regards
    Alastair

  3. #3
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Populating columns for data that "refers to" other data, and is "referred by" other da

    Hi Alastair,

    Thanks for the reply. I'm sorry if my description was confusing, so let me clarify what I'm trying to do. I have a list of several documents (these would be the data in column A). Information within each document may refer to another document - for example the contents of Document_A contains a sentence that says "refer to Document_B and Document_C for more info". So this is what I mean when I say "Refers To" (Document_A refers to Document_B and Document C). The "Refers To" column had to be all entered by manually going through each document and checking their references.

    What I'm trying to do populate the "Referred By". In this example, Document_B is "Referred By" Document_A, but it may also be referred by many other documents within the list of documents in the first column of my spreadsheet.

    What I think the formula should do is (Using Document_A as an example):

    - Search for the string "Document_A" in the "Refers To" column for every row
    - For each instance where "Document_A" appears, return the document name (from the first column) for the corresponding row
    - Concatenate all of the returned document names into a single cell to the "Referred By" column for Document_A

    I added another file showing an exampled of the desired output - hopefully it's more clear in this image.

    Thank you
    Attached Images Attached Images
    Last edited by thho; 03-31-2014 at 08:52 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Populating columns for data that "refers to" other data, and is "referred by" other da

    Hi thho

    Ok I am getting the picture (getting a spreadsheet would be better!)

    A few questions:

    1. How is your data laid out? As in Green or Orange in the attached sheet?

    2. What is the maximum number of possible cross references per Document? (I note you mention "many")

    3. How many documents are we dealing with?

    4. Once the cross referencing is done, will it need updating, or is this a one off procedure?

    I've no idea how to do this, but I will have a better idea once I have received some answers.

    Regards
    Alastair
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Populating columns for data that "refers to" other data, and is "referred by" other da

    Thanks Alastair,

    Sorry I didn't include a spreadsheet, but it's essentially exactly as what was shown in the image.

    1. The data is laid out as you had it in the orange highlighted cells.

    2,3. There are roughly 1400 documents that are in this list, so the max number of cross-references for a single document could be quite large (1399 if one document is referenced by every single other document, but that's very unlikely). A quick search on one of the documents reveals that it's referenced 65 times by other documents. I think a reasonable maximum would be 150, but even that is a little extreme.

    4. Yes, it will need updating. Sometimes documents are rendered obsolete and must be removed/replaced by others.

    Greatly appreciate your help.

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Populating columns for data that "refers to" other data, and is "referred by" other da

    Hi thho

    A picture may well paint a thousand words, but an Excel sheet is even better (especially on an Excel Forum! )

    I have based my maxima on your figures and made 1500 rows with 150 possible cross references.

    I have had to assume that your "Refers to" cell is strictly data/single space/data etc. The macro will refuse to accept anything else - so if you have something else, let me know.

    I have tested it with 200 rows and 150 x-refs and have left your original as a test routine.

    I have put in one error test - if you say that something refers to a name that does not exist in the "Documents names" list, you get the opportunity to add this something to the "Document names". Should you choose to not avail yourself of this opportunity, the macro then sulks and refuses to play any more.

    My previous reference to updating may be ignored - you can run it as many times as you want - it will always give the same answer.

    Let me know how you get on.

    Regards
    Alastair
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Populating columns for data that "refers to" other data, and is "referred by" other da

    Hi Alastair,

    That works great!! Thank you very much! I actually have a new line separator after each document name instead of a space, but I can easily bypass that using the =substitute formula.

    Thanks again, I really appreciate it!

  8. #8
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Populating columns for data that "refers to" other data, and is "referred by" other da

    Hi thho

    Glad it works for you. Perhaps you would mark this a s "Solved" (go to your first post and use the option) - it keeps things nice and tidy.

    Regards
    Alastair

+ 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. Replies: 0
    Last Post: 11-22-2013, 01:36 PM
  2. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  3. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM
  4. [SOLVED] Excel Stock Chart - "Close marker" refers to incorrect data series
    By MartinShort in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-07-2012, 10:50 AM
  5. Recognize "formulas "result" as "typed data", through and through.
    By gandolff in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-13-2009, 01:30 PM

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