How can I combine two tables?

[barcelonat]

Hello - I have two tables across two worksheets. Table 1 has activities/tasks (approx. 200) down column A and named individuals across row 1 (approx. 25) with input data being added into the table, say B2:Z200, so that you can assign one or more named individuals to an activity (and the same individual more than 1 activity) Table 2 has the same activities down column A as Table 1 but Row 1 is now dates - so it creates a sort of programme. What I want to create is a third table that has the programme function (row 1) but has both the activities and names individuals down column A. So rows of activities with sub rows of named individuals who have been assigned to the specific activity under the activity row.

Any advice or support would be greatly appreciated.

Firstly, I am just wanting to know if this is likely possible/simple? I assume I would need to use VBA in order to place the names under the activities in column A?

I can share an example of table 1 and table 2 if helpful?

Thanks,
Nat

 

[etaf]

examples always help - with a sample and also expected results
also what version of excel will this be used on ?

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[barcelonat]

examples always help - with a sample and also expected results
also what version of excel will this be used on ?

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Many thanks etaf - I will do exactly that and upload a sample when I get a chance.

 

[barcelonat]

examples always help - with a sample and also expected results
also what version of excel will this be used on ?

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Hi etaf and co,

I couldn't install XL2BB, so here are a set of copied tables which hopefully give an indication of what Table 3 (will hopefully look like) from Table 1 (and 2) would look like. If this doesn't work i will see if I could install XL2BB on my phone or similar.

Table 1: Stage task, and activities may change, as will number of people, and the values assigned to them

Unique ref.

Stage

Task

Activity

Start

End

Person 1

Person 2

Person 3

Person 4

Person 5

Direct

Total hours

1.1.1

Stage 1

Task 1

Activity 1

05/02/2024​

01/03/2024​

20​

10​

£0​

30​

1.1.2

Stage 1

Task 1

Activity 2

12/02/2024​

19/02/2024​

2​

2​

£50​

4​

1.1.3

Stage 1

Task 1

Activity 3

12/02/2024​

01/03/2024​

5​

5​

25​

£0​

35​

1.2.1

Stage 1

Task 2

Activity 1

13/02/2024​

05/04/2024​

120​

£0​

120​

1.2.2

Stage 1

Task 2

Activity 2

14/02/2024​

19/04/2024​

90​

£1,500​

90​

2.1.1

Stage 2

Task 1

Activity 1

04/03/2024​

05/04/2024​

10​

10​

10​

10​

10​

£0​

50​

2.1.2

Stage 2

Task 1

Activity 2

04/03/2024​

26/04/2024​

8​

8​

8​

£0​

24​

2.2.1

Stage 2

Task 2

Activity 1

08/04/2024​

19/04/2024​

100​

£0​

100​

Total

137​

35​

138​

43​

100​

£1,550​

453​

Table 2: This table it autogenerated from Table 1 (so isn't technically needed for making Table 3) - mostly the start and end date creating the dates in the header row.. Table 2 can be Weekly, or Monthly (based on a simple data validation list ( 'Week' or 'Month'). Typically based on length - a programme of <1 year might be weekly, something much longer (<10 years would be monthly). This shading from the conditional formatting has not pulled through, but it is mostly the dates in the header ow that is needed for Table 3.

Unique ref.

Stage

Task

Activity

Start (W/C)

End (W/C)

05-Feb-24​

12-Feb-24​

19-Feb-24​

26-Feb-24​

04-Mar-24​

11-Mar-24​

18-Mar-24​

25-Mar-24​

01-Apr-24​

08-Apr-24​

15-Apr-24​

22-Apr-24​

1.1.1

Stage 1

Task 1

Activity 1

05/02/2024​

26/02/2024​

1.1.2

Stage 1

Task 1

Activity 2

12/02/2024​

19/02/2024​

1.1.3

Stage 1

Task 1

Activity 3

12/02/2024​

26/02/2024​

1.2.1

Stage 1

Task 2

Activity 1

12/02/2024​

01/04/2024​

1.2.2

Stage 1

Task 2

Activity 2

12/02/2024​

15/04/2024​

2.1.1

Stage 2

Task 1

Activity 1

04/03/2024​

01/04/2024​

2.1.2

Stage 2

Task 1

Activity 2

04/03/2024​

22/04/2024​

2.2.1

Stage 2

Task 2

Activity 1

08/04/2024​

15/04/2024​

Table 3: Any example of what I am after, taking components of both Table 1 and 2 - the actual tables ill have over 200 rows, and 50 plus people, with as much as 8 on any given task, so would be significantly longer - hence hoping to automate the process a little. Ideally I would want 'headings' and 'sub-headings' for the Stage and Task that provided totals and sub-total, but this is not 100% needed - it could just be per activity / based on unique ref as shown below The Programme on Table 2 will be in week, but this typically needs to be months.
The only new data here is the months column that I just took =(YEAR(G2)-YEAR(F2))*12+(MONTH(G2)-MONTH(F2)+1), and then used it to calculate a rough monthly split of values based on totals.

Unique ref.

Stage

Task

Activity

Person

Start (W/C)

End (W/C)

Total

Months

Total per month

Feb-24​

Mar-24​

Apr-24​

1.1.1

Stage 1

Task 1

Activity 1

Person 1

05-Feb-24​

01-Mar-24​

20​

2​

10​

10​

10​

1.1.1

Stage 1

Task 1

Activity 1

Person 2

05-Feb-24​

01-Mar-24​

10​

2​

5​

5​

5​

1.1.2

Stage 1

Task 1

Activity 2

Person 1

12-Feb-24​

19-Feb-24​

2​

1​

2​

2​

1.1.2

Stage 1

Task 1

Activity 2

Person 2

12-Feb-24​

19-Feb-24​

2​

1​

2​

2​

1.1.2

Stage 1

Task 1

Activity 2

Direct

12-Feb-24​

19-Feb-24​

£50.00​

1​

£50.00​

£50.00​

1.1.3

Stage 1

Task 1

Activity 3

Person 1

12-Feb-24​

01-Mar-24​

5​

2​

2.5​

2.5​

2.5​

1.1.3

Stage 1

Task 1

Activity 3

Person 2

12-Feb-24​

01-Mar-24​

5​

2​

2.5​

2.5​

2.5​

1.1.3

Stage 1

Task 1

Activity 3

Person 4

12-Feb-24​

01-Mar-24​

25​

2​

12.5​

12.5​

12.5​

1.2.1

Stage 1

Task 2

Activity 1

Person 3

13-Feb-24​

05-Apr-24​

120​

3​

40​

40​

40​

40​

1.2.2

Stage 1

Task 2

Activity 2

Person 5

14-Feb-24​

19-Apr-24​

90​

3​

30​

30​

30​

30​

1.2.2

Stage 1

Task 2

Activity 2

Direct

14-Feb-24​

19-Apr-24​

£1,500.00​

3​

£500.00​

£500.00​

£500.00​

£500.00​

2.1.1

Stage 2

Task 1

Activity 1

Person 1

04-Mar-24​

05-Apr-24​

10​

2​

5​

5​

5​

2.1.1

Stage 2

Task 1

Activity 1

Person 2

04-Mar-24​

05-Apr-24​

10​

2​

5​

5​

5​

2.1.1

Stage 2

Task 1

Activity 1

Person 3

04-Mar-24​

05-Apr-24​

10​

2​

5​

5​

5​

2.1.1

Stage 2

Task 1

Activity 1

Person 4

04-Mar-24​

05-Apr-24​

10​

2​

5​

5​

5​

2.1.1

Stage 2

Task 1

Activity 1

Person 5

04-Mar-24​

05-Apr-24​

10​

2​

5​

5​

5​

2.1.2

Stage 2

Task 1

Activity 2

Person 2

04-Mar-24​

26-Apr-24​

8​

2​

4​

4​

4​

2.1.2

Stage 2

Task 1

Activity 2

Person 3

04-Mar-24​

26-Apr-24​

8​

2​

4​

4​

4​

2.1.2

Stage 2

Task 1

Activity 2

Person 4

04-Mar-24​

26-Apr-24​

8​

2​

4​

4​

4​

2.2.1

Stage 2

Task 2

Activity 1

Person 1

08-Apr-24​

19-Apr-24​

100​

1​

100​

100​

 

[etaf]

i have looked and not sure how to do this - sorry

 

[barcelonat]

i have looked and not sure how to do this - sorry

No problem - I have thought of a sort of workaround currently using formulas to generate the list of activity per person for all combinations (18K rows), and will look look into a macro to just remove rows that are not used. It’s just not so robust.