Taking one value from a list on one sheet and duplicating it multiple times in a list in another

[RichieA]

Hi all - a data entry question but on a scale that needs automation/formula to build:

In the example below, Sheet 2 is my master source of clients and values and Sheet 1 I am building from scratch as a sort of Account Plan/Summary "database" with actions (in the absence of a company CRM system). You can see from Sheet 2 that there is One row entry per Client name (of which there are 600+). In Sheet 1 though, I need to reflect these but need circa 14 row entries per client. Is there an easy way of automating this with a formula? Macros I know NOTHING about!

So I could build it so that the 14 Offices in Column B in sheet 1 are already entered and copied down the relevant number of times (600+) and then need to populate Column A with the relevant Client in blocks of 14. Does that make sense?

Or is there a way to tackle building it with 14 row entries per Client (from Column A in Sheet2) AND a corresponding Office entry next to it (from the column headers in Sheet 2?

Many thanks

RichieA

Sheet 1
Client	Office	Value	Next Action	Latest Event
Alpha Inc	Austria	£25	Arrange meet	Sold 1000 units
Alpha Inc	Belgium	£63	Supply goods	Visit complete
Alpha Inc	Canada	£564	Supply goods	Visit complete
Alpha Inc	Denmark	£34	Shut down	Shut down
Alpha Inc	Egypt	£67
Bravo Inc	Austria	£42
Bravo Inc	Belgium	£40
Bravo Inc	Canada	£32	Rugby	Lunch
Bravo Inc	Denmark	£56
Bravo Inc	Egypt	£40
Sheet 2
A1	B1	B2	B3	B4	B5
	Austria	Belgium	Canada	Denmark	Egypt
Alpha Inc	25	63	564	34	67
Bravo Inc	42	40	32	56	40
Charlie Inc	23	29	45	61	21
Delta Inc	33	568	98	52	231
Echo Inc	97	238	56	21	47

<tbody>
</tbody>

 

[RichieA]

OK -so whilst I can't supply company data, I have sanitised, but the layout isidentical to the original with data in the correct Columns etc.


Sheet 1 named "Clients & Teams". This is the sheet that Ineed to insert the multiple entries (17) per client name that is held withinSheet 2 named "Clients". The client names need to go in Column D. InColumn E, I have shown the list of 17 company teams through almost 2 cycles toshow the pattern. So when correctly populated there should be in Column D 17 entries for Abraham, against each of the Teams, then 17 entries for Allan and so on.

Sheet 2 "Clients". Here, this is the simple list of 20 clientnames for the example. The actual data runs to 600+. For certainty, it will beheld as a separate sheet within the same workbook as Sheet 1.

Mick - massively grateful for your input. As a learning exercise, if there isthe potential to add some narrative explaining what your code is doing, thatwould help me understand the workings a little better. I'd like to be able toget to a point of self-sufficiency rather than relying on the awesome advicefrom helpful people on this site (though that may be very aspirational!).

If you foresee any difficulty with the way I have represented the data - ie thecode won't work because it's still not a complete sheet, please let me know.With my basic logic, I assume that I could make small changes to the code toreflect more rows or a different sheet name - in the same way one would withformulas. But I have no code knowledge, so not sure if it works the same way.

Many thanks in anticipation

RichieA


SHEET1

			Progress & Actions
A	B	C	D	E
£ 2014 YE	£ 2015 H1	Progress	Client	Team
		2		Team1
				Team2
				Team3
				Team4
				Team5
				Team6
				Team7
				Team8
				Team9
				Team10
				Team11
				Team12
				Team13
				Team14
				Team15
				Team 16
				Team17
				Team1
				Team2
				Team3
				Team4

<colgroup><col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;" width="69"> <col style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;" width="42"> <col style="width: 179pt; mso-width-source: userset; mso-width-alt: 8704;" span="2" width="238"> <tbody>
</tbody>

SHEET2

A	B
RANK	CLIENT
Rank	Company
1	Abraham
2	Allan
3	Alsop
4	Anderson
5	Arnold
6	Avery
7	Bailey
8	Baker
9	Ball
10	Bell
11	Berry
12	Black
13	Blake
14	Bond
15	Bower
16	Brown
17	Buckland
18	Burgess
19	Butler
20	Cameron

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 248pt; mso-width-source: userset; mso-width-alt: 12105;" width="331"> <tbody>
</tbody>

 

[MickG]

From what you now show in sheet (1) & (2), directs me to alter my code so that each "Clients" name (from Sheet2) would be copied down column "D" of sheet1, 17 times. Is that what you want ????, And if so where would I get the "Team" names from ????

Whereas the original code Looked at each client in sheet2, then looked at the exact number of entries in that row, and took those entries along with the appropriate headers and places them in sheet1 columns A,B and C. Can I assume you do not now want this and would go for the Code in Paragraph above, ???

 

[RichieA]

Ah - I see! Apologies for the confusion.

In answer to your question in para 1 "From what you now show in sheet (1) & (2), directs me to alter my code so that each "Clients" name (from Sheet2) would be copied down column "D" of sheet1, 17 times. Is that what you want ????" In short, Yes. Because it turns out my master sheet (Sheet2) only includes 14 of the 17 Teams I require, I figured it was easier to manually enter the 17 teams in Sheet1 and then just copy the sequence down multiple times using the "fill handle". I guess that makes it easier - with the code not having to compute how many entries in another sheet etc?

So I think the code in Para1 would suffice.

(Let me know if you think my method of copying down the sequence of 17 teams using the fill handle is long-winded and could be done simply using anothetr method).

Many thanks Mick

R

 

[MickG]

If you specify what the teams are, say, in sheet 2, the code could easily fill them, while it filling the clients. Just as you like !!!!
Let me know and I'll send some code.

 

[RichieA]

OK - so use Sheet 2 B703:719 for the 17 Team names, and I'll populate those cells before running the code in the actual worksheet.

Thanks for your patience!

R

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26Aug04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, TeamRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Clients")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("B3"), .Range("B1").End(xlDown))
    [COLOR="Navy"]Set[/COLOR] TeamRng = .Range("B703:B719")
[COLOR="Navy"]End[/COLOR] With
 c = 2
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]With[/COLOR] Sheets("Clients & Teams")
            TeamRng.Copy .Cells(c, "D")
           .Cells(c, "C").Resize(17) = Dn
        [COLOR="Navy"]End[/COLOR] With
 c = c + 17
 [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[RichieA]

Awesome Mick - that works, thank you! I had to tweak slightly to make the data appear in columns D & E instead of C & D - but that made me try and understand the code, so very useful.

Mick - many thanks for all your help, I'm learning here that Excel is much more capable than I previously thought. Whether I can now get internal sponsors to understand the power of what I'm compiling remains to be seen...!

Presumably now the macro has been run to create what I need, I don't need to save as a macro-enabled worksheet as it doesn't need to be dynamic going forward?

Thanks again

RA

 

[MickG]

You're welcome
NB:- If you've placed the code in a new workbook , then you need to Save as "xlsm" to save the code, otherwise no.