Creating a template

[sauce1979]

I am currently trying to create a template for user to fill in. i have data as follows:

[B]D_AC        D_RU          D_SBU     f00[/B]
501          207         90001      34
502          208         90021      54
501          208         90001      44
501          208         90312      84
501          209         90001      54
501          209         90211      54
501          210         90021      64
501          210         90001      54


[B]D_RU        D_ENT[/B]
207            H_YJ
207            H_EY
207            H_CY
208            H_EY
208            H_EJ
209            H_EZ
209            H_OO
210            H_YY
210            H_TY
207            H_EY

these two sets of data are on 2 different worksheets. The first is called base data, the second entity. There are further worksheets which contain all the data for a given D_RU. Thus there is a worksheet called 207 which holds all the records where the D_RU = 207 and so on so forth.
The value in column f00 is split between entities which are listed in the entity worksheet depending on the RU number. I need to provide a template which will allow a user to enter the split and then add the new split lines to the base data.

For example:

In the 207 worksheet three more columns should be added to the row where the D_RU IS 2O7. The three rows should be HYJ,H_EY, H_CY as they represent the 3 entities associated with 207. A user can then split the value f00 between those columns. There should be a check which will ensure the sum of those entity colum = f00. Wherever there is a data under entity column added this data should be added to the base data work sheet as a new row. This is shown below.

[B]D_AC        D_RU          D_SBU     f00     H_YJ      H_EY     H_CY[/B]
501          207         90001      34     10           20        4

The base data should then change to:

[B]D_AC        D_RU          D_SBU     f00   D_ENT[/B]
501          207         90001      10      HYJ
501          207         90001      20      HEY
501          207         90001      4       HCY
502          208         90021      54
501          208         90001      44
501          208         90312      84
501          209         90001      54
501          209         90211      54
501          210         90021      64
501          210         90001      54

or something to that effect.
I am not that familiar with excel so any help would be much appreciated

 

[pbornemeier]

Working on questions

 

[sauce1979]

The process should be in 2 stages. First the entities should be listed associated with the RUs should be listed on the appropriate worksheets. This could be done by a button or macro etc. These will then be given to users to populate their f00 splits.

When users fill in their data under the given entities the data should then be pumped back into the base data worksheet.

It is possible that H_JY will be in the base data more than once. Data should always be to the base data worksheet. From my data, tha maximum number of entities associated with 1 ru is 10

 

[pbornemeier]

From what you said, I believe you want then code to:
1) Examine each row on the Entity worksheet.
2) Copy the D_ENT value to the first row of the leftmost empty column on the worksheet identified by D_RU (207 for example).
...A) What should happen if H_YJ was already on the 207 worksheet? Add to existing value? Overwrite?
...B) What happens when you run out of columns on the 207 worksheet?3) Delete the row from the Entity worksheet.
4) Continue 1,2,3 until Entity worksheet is empty
5) For the D_RU worksheets where columns were added, prompt user to enter data in row 2 of the new columns and ensure numeric values add to the f00 value.
6) Continue 5 until all sheets edited.
7) If only one D_ENT was added update the existing row on the base data worksheet
8) If more than one D_ENT was added, do 7 andaAdd additional rows on the base data worksheet as shown.

...C) Before your example started, are the various D_RU worksheets already populated with data?

D_AC        D_RU          D_SBU     f00
501          207         90001      34

...D) Before your example started, what does the 208 worksheet look like?
...E) You said: "There are further worksheets which contain all the data for a given D_RU" and
"In the 207 worksheet three more columns should be added to the row where the D_RU IS 2O7."
Are there other rows on the 207 worksheet?
...G) What happens tomorrow when one more D_RU 207 value is on the Entity worksheet? The f00 has already been distributed to the first 3 new columns. Should the original f00 value then be distributed across all 4 items?
...H) In your example, the base data shows 3 rows with the D_RU of 208 and 2 D_RU 208 items on the Entity worksheet where do they go on the 208 worksheet.
Please let me know if my guess at what you want is correct. Please reply to the lettered questions.

 

[sauce1979]

For question 2:

A)
all the D_ENT values for a given d_ru on the entity worksheet should be copied to the rightmost coluum on the worksheet. You should not overwrite as H_YJ will only be associated with 207.
B) We should not run out of columns as the maximum mount of colummns is 20

3) the row should not be deleted from the entity worksheet

For Question 8:

C) The D_RU worksheets are already populated

D) THE 208 worksheet looks exactly like the 207 worksheet except it has lines which have a D_RU of 208

E) There are no ther columns. The three columns which should be added correspong to the 3 D_ENTs asscoitated with 207.

G)The orginial f00 value should be distributed across 4 items

H) Exactly the smae process that happened with the 207 shjould happen for all D_RUs. My example only illustrated for one D_ru 207. For 208 2 columns should be added to the rightmost column. If both columns are filled the, 2 rows should be added to the base data where the original f00 is distributed between the 2 rows

 

[pbornemeier]

There are 3 D_RU 208 in the base data there are 2 D_RU in the entity. Which of (Should all) the base D_RU lines should be duplicated? How should the 3 base data f00 be handled?

 

[sauce1979]

In the case of 208, the 2 lines for d_rus in the entity worksheet mean there are 2 possible entities which the f00 in the base data can be split between. Therefore the 3 base data rows should be duplicated a maximum 2 times depending on how the f00 data is split.
the rows from the base should be the same except the f00 changes an extra column called d_ent is created with the d_ent name. if there f00 data is plist between 2 entities, then there should be 2 lines like this.
Thinking about it a bit more, maybe the base data data should stay the same and the copied rows should be put into a new worksheet.

 

[pbornemeier]

For the data you have in the first post, please show how each of the worksheets should look after they are processed: Base Data, Entity, 207, 208, 209, 210 and the new sheet you mentioned in post #7.

I understand that the breakup of f00 is arbitrary, but pick a valid distribution.

If the Entity worksheet is not cleared (with processed rows being deleted or copied to a new worksheet), then they will be re-processed each time the procedure is run.

 

[sauce1979]

After processing Base data and entity should look like this:
Code:

[B]D_AC        D_RU          D_SBU     f00[/B]
501          207         90001      34
502          208         90021      54
501          208         90001      44
501          208         90312      84
501          209         90001      54
501          209         90211      54
501          210         90021      64
501          210         90001      54


[B]D_RU        D_ENT[/B]
207            H_YJ
207            H_EY
207            H_CY
208            H_EE
208            H_EJ
209            H_EZ
209            H_OO
210            H_YY
210            H_TY

the worksheets should lik like as follows I have assumed that users has entered data for every entity

WORKSHEET 207

[B]D_AC        D_RU          D_SBU     f00     H_YJ      H_EY     H_CY[/B]
501          207         90001      34     10           20        4


WORKSHEET 208

[B]D_AC        D_RU          D_SBU     f00     H_EE      H_EJ [/B]    
501          208         90021      54     20           34    
501          208         90001      44     10           34    
501          208         90312      84     42           42

WORKSHEET 209 
  
[B]D_AC        D_RU          D_SBU     f00     H_EZ      H_OO [/B]    
501          209         90021      54     24           30    
501          209         90211      54     20           34  

WORKSHEET 210
 
[B]D_AC        D_RU          D_SBU     f00     H_YY      H_TY[/B]     
501          210         90021      64     32           32    
501          210         90001      54     20           34

The output worksheet should look like:

[B]D_AC        D_RU          D_SBU     f00   D_ENT[/B]
501          207         90001      10     H_YJ
501          207         90001      20     H_EY
501          207         90001      4      H_CY
502          208         90021      20     H_EE
502          208         90021      34     H_EJ
501          208         90001      10     H_EE
501          208         90001      34     H_EJ
501          208         90312      42     H_EE
501          208         90312      42     H_EJ
501          209         90001      24     H_EZ
501          209         90001      30     H_OO
501          209         90211      20     H_EZ
501          209         90211      34     H_OO
501          210         90021      32     H_YY
501          210         90021      32     H_TY
501          210         90001      20     H_YY
501          210         90001      34     H_TY

I Take your point about moving the entity data to another worksheet

 

[pbornemeier]

There were some D_AC 502 values in the base data, but none on the 207-210 worksheets - I am assuming that was an omission on your part and they should be 502 there.

Before any processing, I am assuming that the D_AC D_RU D_SBU f00 columns are filled in on the 207-210 worksheets.

Are these assumptions correct?

It appears as if all of the data on the after-processing version of the 207-210 worksheets can be generated from the base data and entity worksheets. Is there any reason not to start with only the base data and entity worksheets then create the 207-210 and output worksheets?