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>

 

[DWig]

Will it always be the same number of rows required per client?
So in this case, exactly 14 iterations of each client with the same set of offices for each?

 

[RichieA]

Yes. The Offices "list" won't change. There may be some initially that have no relevant info but my view is I can then filter out blank ones afterwards - the point being that as accounts grow, technically every office should have some info to add.

 

[RichieA]

Though also please look at this requirement for the same spreadsheet re: breaking a row up - or breaking into it to reflect multiple actions per office: A mix of Merged/Split Cells in a row to reflect a "family" of separate actions relatint to a "parent" cell? (er, how do i link to another thread in this forum)

 

[MickG]

Try this, for your basic requirements:-
Results sheet1.

[COLOR="Navy"]Sub[/COLOR] MG21Aug56
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] Range, AcRng [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] cols [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
cols = .Range("A1").CurrentRegion.Columns.Count
ReDim ray(1 To Rng.Count * cols, 1 To 3)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Set[/COLOR] AcRng = Range(Cells(Dn.Row, 2), Cells(Dn.Row, Columns.Count).End(xlToLeft))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] AcRng
        c = c + 1
        ray(c, 1) = Dn.Value
        ray(c, 2) = Cells(1, Ac.Column)
        ray(c, 3) = Ac
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
Sheets("Sheet1").Range("A2").Resize(c, 3).Value = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[RichieA]

Mick - thank you for your response. Forgive my basic working knowledge - this "code", where does it go? Should it just be rewritten - if so where? Or can it be copied and pasted - again, if so where should it be pastd to?

Many thanks

Richard

 

[MickG]

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.


On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.

NB:- You could also Copy the code to a Command Button, and run the code from there
Regrds Mick

 

[RichieA]

Ok - thanks for that, Mick. I'm way out of my depth! I got the code to run but of course it was written for my example sheets. When used in my live sheets the references and sheet names are different. With a formula at least i can work out what pieces have what function. But I can;t make head nor tail of how the code works to be able to make the relevant tweaks. Do you have any suggestions?

many thanks

R

 

[MickG]

Please send an example of you ACTUAL data, with explanatory notes as required.

 

[RichieA]

Mick - many thanks indeed. I'm out of the office today but will get this to you tomorrow. Really appreciate your help, as this process will help build the sheet correctly the first time and it only needs to be done once.

Regards

R