Generate subset table from Master table

[Nick30075]

I have a table that looks like this:
Need Col D values that have ";" present to be parsed out to create and new worksheet with the results looking like the output below.

I've looked & tried but is beyond my level at this time. Any & all help is greatly appreciated!

Excel 2010
	A	B	C	D
1	ID	System Name	Name	Predecessors
2	1	Sys1	Task1	0
3	2	Sys2	Task2	1
4	3	Sys3	Task3	2
5	4	Sys4	Task4	5
6	5	Sys5	Task5	0
7	6	Sys6	Task6	0
8	7	Sys7	Task7	5
9	8	Sys8	Task8	6
10	28	Sys9	Task9	23;27;8
11	7	Sys10	Task10	27
12	23	Sys11	Task11	0
13	27	Sys12	Task12	0
Sheet4

Need output like this in a new worksheet:

Excel 2010
	A	B	C	D
1	New sheet results
2	ID	System Name	Name	Predecessors
3	28	Sys9	Task9	23;27;8
4	23	Sys11	Task11	0
5	27	Sys12	Task12	0
6	8	Sys8	Task8	6
Sheet1

 

[Nick30075]

Hi WarPigl3t,
We are all but there, thanks for everything so far!

CopyPaste bolded below is generating a Compile error: syntax error.

Sub pastePredecessorRows(dataSheet, outputSheet, columnSearch, firstRow, lastRow, pasteRow, myValue, columnID)
     mySplit = Split(myValue, ";")
     For Each element In mySplit
          r = firstRow
          myValue = Sheets(dataSheet).Range(columnID & r).Value
          Do Until r > lastRow Or element = myValue
               r = r + 1
               myValue = Sheets(dataSheet).Range(columnID & r).Value
          Loop
          If element = myValue Then
               [U][/U][B]CopyPaste(dataSheet, outputSheet, r, pasteRow)[/B]
               pasteRow = pasteRow + 1
          End If
     Next element
End Sub

 

[WarPigl3t]

In regards to the compile error on this line.

CopyPaste(dataSheet, outputSheet, r, pasteRow)

My bad. That's not how you call a subroutine and CopyPaste is a subroutine. That's how you call a function. Here's how you call a subroutine.

Call CopyPaste(dataSheet, outputSheet, r, pasteRow)

Haha I know. such a simple solution.

 

[Nick30075]

WarPigl3t,
Seems the pastePredecessorRows sub is not pasting anything.

Can see the elements cycle thru the myvalue splits but nothing ever pastes to the new sheet

  : element : "23" : Variant/String

When MyValue=23 the loop kicks in and changes MyValue to the next element (27) without pasting to new sheet.

Thanks!

 

[Peter_SSs]

I have a table that looks like this:
Need Col D values that have ";" present to be parsed out to create and new worksheet with the results looking like the output below.

I've looked & tried but is beyond my level at this time. Any & all help is greatly appreciated!

Excel 2010
	A	B	C	D
1	ID	System Name	Name	Predecessors
2	1	Sys1	Task1	0
3	2	Sys2	Task2	1
4	3	Sys3	Task3	2
5	4	Sys4	Task4	5
6	5	Sys5	Task5	0
7	6	Sys6	Task6	0
8	7	Sys7	Task7	5
9	8	Sys8	Task8	6
10	28	Sys9	Task9	23;27;8
11	7	Sys10	Task10	27
12	23	Sys11	Task11	0
13	27	Sys12	Task12	0
Sheet4

Need output like this in a new worksheet:

Excel 2010
	A	B	C	D
1	New sheet results
2	ID	System Name	Name	Predecessors
3	28	Sys9	Task9	23;27;8
4	23	Sys11	Task11	0
5	27	Sys12	Task12	0
6	8	Sys8	Task8	6
Sheet1

Some clarification questions:

1. Will there be only one cell in column D that contains semicolons? If not, does each set get parsed out to a separate new sheet, or do they all go into columns A:D of just a single new sheet, something else?

2. If the semicolon list in your sample was "23;27;7" then what row(s) would get pulled to the new sheet given that you have two rows with ID = 7 and those rows contain different data?

3. Does the order of the rows in the new worksheet matter? That is, would it matter for your example if the result sheet was in the ID order: 8,23,27,28 or 28,8,23,27?

 

[WarPigl3t]

After reviewing the code again, I am unsure why it is never entering the if statement when element = myValue. Sorry.

Can you tell me what element and myValue are equal to when the code exits the loop right before it hits the if statement?

 

[Nick30075]

Responses to Questions:
1. Will there be only one cell in column D that contains semicolons? If not, does each set get parsed out to a separate new sheet, or do they all go into columns A:D of just a single new sheet, something else?
No there can be multiple instances of multi ";" delimited values in col D.
Parsed into the A:D columns in the single new worksheetsheet

2. If the semicolon list in your sample was "23;27;7" then what row(s) would get pulled to the new sheet given that you have two rows with ID = 7 and those rows contain different data?
Would pull the original row containing the semi colon values in Col D
Would pull the row that has 23 in col A
Would pull the row that has 27 in col A
Would pull the row that has 7 in col A

3. Does the order of the rows in the new worksheet matter? That is, would it matter for your example if the result sheet was in the ID order: 8,23,27,28 or 28,8,23,27?
Order will not matter


The sequence is this:
Find the values in Col D that have semi-colons present
For Col D cells that contain ; copy the initial row where the occurance is present into a new worksheet then
copy the rows of the values contained in the ";" delimited cell to the new worksheet

Thanks!

 

[WarPigl3t]

Okay that didn't answer my question so I'll assume you don't know how to retrieve values from variables and let Peter help you from here.

 

[Nick30075]

Element = "23" (Variant/String)
myValue =23 (Variant/Double)

 

[Peter_SSs]

Responses to Questions:

2. If the semicolon list in your sample was "23;27;7" then what row(s) would get pulled to the new sheet given that you have two rows with ID = 7 and those rows contain different data?

Would pull the row that has 7 in col A


3. Does the order of the rows in the new worksheet matter? That is, would it matter for your example if the result sheet was in the ID order: 8,23,27,28 or 28,8,23,27?
Order will not matter

The red part above is written in the singular so does not address my question. There were two rows in the original data with 7 in col A. Do they both get pulled to the new sheet? If not, which one & why?



In relation to "order does not matter", if the data was as I have shown in columns A:D below, which results in columns H:K would suffice?

H1:K8
That is results in any order.
Easiest to achieve.


H11:K18
Each 'set' of results together, but in any order.
Next easiest.

H21:K28
Semicolon line first, followed by related items in any order.
Least easy.



Colours are just to make identification easier while testing.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	ID	System Name	Name	Predecessors				ID	System Name	Name	Predecessors
2	1	Sys1	Task1	0				1	Sys1	Task1	0
3	2	Sys2	Task2	1;29				2	Sys2	Task2	1;29
4	3	Sys3	Task3	2				8	Sys8	Task8	6
5	4	Sys4	Task4	5				28	Sys9	Task9	23;27;8
6	5	Sys5	Task5	0				23	Sys11	Task11	0
7	6	Sys6	Task6	0				27	Sys12	Task12	0
8	7	Sys7	Task7	5				29	Sys13	Task13	0
9	8	Sys8	Task8	6
10	28	Sys9	Task9	23;27;8
11	7	Sys10	Task10	27				ID	System Name	Name	Predecessors
12	23	Sys11	Task11	0				1	Sys1	Task1	0
13	27	Sys12	Task12	0				2	Sys2	Task2	1;29
14	29	Sys13	Task13	0				29	Sys13	Task13	0
15								8	Sys8	Task8	6
16								28	Sys9	Task9	23;27;8
17								23	Sys11	Task11	0
18								27	Sys12	Task12	0
19
20
21								ID	System Name	Name	Predecessors
22								2	Sys2	Task2	1;29
23								1	Sys1	Task1	0
24								29	Sys13	Task13	0
25								28	Sys9	Task9	23;27;8
26								8	Sys8	Task8	6
27								23	Sys11	Task11	0
28								27	Sys12	Task12	0
29
Sheet1

 

[Nick30075]

Hi Peter,
Apologies, the occurance of '7' twice in col A is an error (dummy data).
The Column A 'Id' value will contain unique values, no duplicates.

Ideally the results would be the H21:K28 sequence.
However as it is the most complex design the H11:K18 & H1:K8 will be more than adequate.
So whatever your time allows for I'll be very appreciative of the effort!

Many Thanks!