-2

My question is if there is any function in Excel that can solve my problem or if I should go with a programmatical approach.

I've got an Excel sheet with two columns. These columns represent a relationship between the items on the row. We can call it left-right relationship. This is spread on many rows but I want to lay it out in a single row with many columns.

If I got the rows:

123  -- 123A

123B -- 123A

123C -- 123B

I want to create a table which looks like this: 

123A -- 123 -- 123B --123C

The fact that 123 doesn't have a direct with 123B is irrelevant, just that they are all related to 123A.

Are there any built-in functions I can use or should I solve this with vba?

Improve this question
2
  • I'm confused, you just want to match strings and put them in a single cell? With no regard for order?
    – Raystafarian
    Dec 1, 2015 at 12:23
  • Not a single cell, another book/sheet or table. Column 2 contains duplicates. Now I want to take all column 1 entries that correspond to a column 2 entry and insert in a new table as a single row but in separate columns. Then recursively check if the entry from column 1 also exists in column 2. If it does append these in to the new table and keep doing this until there are no entries left matching. Each unique entry in column two would then contain all entries linked together but the order isn't relevant, only that there exists a relationship.
    – Yo444
    Dec 1, 2015 at 12:46

1 Answer 1

Reset to default
0

I am not aware of any built-in functions that will do the job. The challenge is that you can have any number of items related, and the formula language doesn't handle situations where you don't know the maximum number of items in advance.

In similar situations, I have used VBA functions storing objects of custom classes in Collections. The custom class can hold a row number and the text of the item.

This particular problem is one of determining "equivalence classes" from the pairs of items. Each row of the output is an equivalence class. This, section 3, is one reference, but I can't find a code sample off-hand. I know it's in Knuth, but so is everything :) .

Improve this answer
1
  • Thanks, wasn't looking for a solution but to determine if it would be a waste of time to code when there might be some ready available tools. Since the format is csv of the file I'll just go with c#. Then I can just iterate the file and push every relationship onto a stack and go from there. Should be able to get it done quickly.
    – Yo444
    Dec 1, 2015 at 13:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .