[ab11viva]

Hello,

I have an issue with an Excel sheet that manages 2 types of procedures. One type (GPBs) already has existing references imported into the table in Column C. These references are to the other type of procedure (PQSs).

The 2 types of procedures are all listed in one column (A) with their type in column B, reference to PQS (for GPB docs) in column C and reference to GPB (for PQS docs) in Column D.

Since the references already exist one way (GPB to PQS), I want to know how to populate column D to capture these same references in the other direction. (i.e. if a GPB references a PQS, the PQS should be shown as referencing the GPB)

Please see attached as I know that description was not ideal. My biggest issue is that some cells contain multiple values and while I have only shown the minimum amount of columns, there are actually about 15-20 and I do not want to drastically change any formatting.

I'm pretty familiar with Excel but I am not well versed in macros. (if that's what's needed)

Thanks

[Zack Barresse]

Hi there, welcome to the board!

I'm not sure I understand what it is you are asking here. Column D is already populated. Are you looking for a formula which will look at cols A, B, and C, then give an expected result? Perhaps if you gave a few examples along with their expected results, it would help.

[ab11viva]

Hi Zack,

You are correct that in some cases Column D is populated. Basically, this was done for a small amount of the procedures.

Column A is a list of all procedures numbers. (these values also make up column C and D as references) What I am looking for is for these references to be 2-way. (i.e. If X references Y, then Y should automatically reference X)

For Row 3, col A (C1000) references Q1221. What I want the spreadsheet to show is the row for Q1221 (298 in this case) also referencing C1000 in column D. (since it is a GPB)

The attached tables might also help as the first table is a sample starting point and the second table shows the complete references in red.


If this is not possible, perhaps we can build a search macro for a procedure number that displays all referenced col A values?

Thanks

[Zack Barresse]

Can you detail out exactly what you are trying to do? Are you wanting to change values of the cells in relation to the matrix you posted (reference table)? Please give 5 or 6 examples if possible. And this is to run one time only? Or will you be doing this multiple times?

[ab11viva]

Ideal scenario would be that every time an update is made to column C or D (the references), that this would be reflected in the referenced procedure's row as well. (i.e. where the referenced procedure is listed in col A) So, if procedure B (col A) had a reference added to procedure C, then the row with procedure C (col A) would be updated to reflect the relationship to procedure B. (i.e. 2 updates would not be needed but both procedures would reflect the proper references.

I'm not sure there is a better way to explain it so I apologize if I'm not being clear. This may not even be possible.

An alternative would be a search macro that could be "called" to find all rows where a given procedure is referenced. This might be an easier solution.

[Zack Barresse]

Well, here's the thing, we can do almost anything you want, possibility is almost endless. What isn't endless is my understanding, which I just flat don't here. Can you please explain, as detailed as you can, how you use this spreadsheet? It's very confusing. Please be as detailed as you can. The more you tell us how this works the easier it will be to help you with a solution.

[ab11viva]

alright, Zack - I'll try to start from the beginning.

We have a list of procedures that contain 2 types (PQS and GPB). All procedures will be listed Col A with a distinct row. (i.e. 1 row per procedure) The procedure type is listed in Col B. These procedures can reference any number of other procedures (all of which should be listed in Col A) and these references are captured in columns C and D, depending on the procedure type. (col C is for PQS, col D is for GPB)
Assuming that there have never been any references listed (col C and D are blank), I want to create a spreadsheet where when a user adds a reference to the sheet (i.e. A references B) that the reverse relationship is automatically displayed (i.e. B references A).

If you look at the Reference Table spreadsheet (the simpler of the two that I attached), I show an initial table that would be populated by a user to identify references of the 5 documents shown (A-E). What this first table doesn't display is the relationship that I am looking for which is if X references Y then Y needs to reference X. The second table on this attachment shows the desired output where the document names labelled in red have been displayed. In this example, row 3 shows document B (a GPB) referencing another GPB, document A in col D. The updated table then shows row 11 (document A) now referencing documen B in column D.

While my example shows 2 tables, this would actually be one table where data could be input and the reference cells adjust to handle any new or edited values.

Let me know if I'm getting any closer to conveying what I'm looking for.

Thanks

[Zack Barresse]

Okay, thanks for the explanation. Next I'd like to see if you can give me a few specific examples of the expected outcomes. From what it sounds like you want this to happen automatically, correct? So I think we might be looking at a change event, so that when something is entered, it checks for similar references and makes the appropriate marking in those cells. Are you looking to run this one time, or have it run automatically over time as you enter the data?

[ab11viva]

Zack,

Sorry - I've been out of town for a few days.

I would like this to run automatically. (i.e. a user can enter a new reference (A to B) and the reverse reference (B to A) is automatically populated)

The easiest way that I can give examples is probably in my previous 'reference table' spreadsheet where the first table represents user entries (i.e. manual population of references) and the second table reflects the updates that I would like the spreadsheet to make in red. So, if the row that represents document B (a GPB) is updated with a GPB reference to document A, then the row that represents document A should be updated with a GPB reference to document B.

Also, since the row that represents document E (a PQS) is updated with a GPB reference to document B, then the row that represents document B needs to be updated with a PQS reference to document E.

Thanks
Adam

[Zack Barresse]

Okay, but can you give me, say at least 2, specific examples from the workbook you've attached? I'm talking complete with cell references and all? It would help me get a visual. There is also the question of multiple entries in the cells. I'm assuming you want to parse through them all?

For example, in your 'Procedure reference.xls' file, on the 'GBPs' sheet, cell A2 has a value of 'BI1000'. The reference is to a PQS 'Q1238'. So what you're wanting to do is find all references of 'Q1238' in column A, and on those rows in column D add the 'BI1000' reference (a GBP reference)? Please let me know if I understand that correctly, as an example with your data.

[ab11viva]

Zack,

You are correct that the multiple references would need to be parsed. Please see below for a couple of detailed examples:

Row 4 contains a GPB value (A4 value = C1001, B4 = GPB) and has a reference to a PQS (C4 = M3106). This PQS is listed on row 248 (A248 = M3106). This row needs to be updated to include a reference in D248 to a value of C1001. (D248 was determined, as opposed to C248, because the referenced document is a GPB as indicated by B4)

For rows 3 (C1000), 5 (C1002) and 6 (C1003), there is a reference to Q1221 in the C column. A298 contains this Q1221 value and this row needs to have a reference in D298 to all 3 documents listed above.

For A20 (QS0001, a GPB), there is a PQS reference (C20) to 2 documents.
A291 (Q1213) and A311 (Q1236) row should be updated to contain a reference in D291 and D311 to the A20 value (QS0001).

For the second row, the reference may be incorrect as C2 (Q1238) does not have a corresponding A value. I'm not sure how this would affect things but if it's easier to ignore that row, please do so.

I hope this helps.

[Zack Barresse]

Great examples! Thank you! That's perfect.

Here's part of the problem we're facing. Some of the cells in column C and D have different separators, so we're going to have problems parsing that data out. Some are separated with spaces, some with other special characters. Most of them look like their either character numbers 32 or 49. You should find one way to do it though, whether that's by spaces and putting Wrap Text on the cells, or some other way, but it should be the same. Is that possible?

[ab11viva]

Zack - I believe I have removed all special characters and aligned the formats (i.e. one space between each reference). Let me know if that helps.

Thanks!

[Zack Barresse]

Okay, I don't know if I'm confusing myself or not, but I have a question. Take row 3. Value of C1000 and is (by col B) a GPB type. It has a PQS reference to Q1221. I understand that you want to find the Q1221 in column A and ensure C1000 is added to column D. But what if there is a value in D3 (a GPB reference)? Shall it also find Q1221 and put that reference in column C then? If this is the case, then we would need to match both columns A and B dependent on the reference type. Currently the code I have doesn't take that into consideration.

Right now it looks at column C or D depending on what column B states. So if there is a GPB in column B it will look at the values in column C, find those values individually in column A and make the change to those found cells in column D. Is this correct or no??

[ab11viva]

The type of reference (col B) that is associated with the col A value will determine whether that col A value is added to Column C or D in the corresponding reference. (i.e. column B does not determine col C and D for that row but instead determines where that A value is placed in the referenced rows) For your question, if there was a value in D3, then we would want to find that corresponding value in col A and add a C1000 reference to column D. (because C1000 is a GPB type) The relationship between col C and col D values is not important and does not need to be considered. Only the relationship between values in col C to col A and from col D to col A. (I'm not sure if that is going to help our hurt things... might still be confusing)

Let's take row 16 as a good example. (we'll assume that all other rows are not populated with references) C16 has a reference to L6103. We will want to locate this row that has L6103 as its A value. (in this case A229) and insert the proper reference back to A16. This is where the B16 value comes into play. Since it is a GPB, we know know that this value (PAT1001) should be placed in D229 as it is a GPB reference. Similarly, we want to locate the row that is referenced in D16 (PAT0001 - in this case A15). The A16 value should now be placed in D15 because it is a GPB reference. As you can see, despite the fact that these 2 references were different types, the type of the A value dictates where the code should place it.

Is that any clearer... or still a little murky?