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

R

RichieA

New Member
Joined
Aug 20, 2015
Messages
16
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


[TABLE="width: 554"]
<tbody>[TR]
[TD="class: xl415, width: 184, bgcolor: transparent"]Sheet 1
[/TD]
[TD="width: 116, bgcolor: transparent"][/TD]
[TD="width: 101, bgcolor: transparent"][/TD]
[TD="width: 115, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #CCC0DA"]Client
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Office
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Value
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Next Action
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Latest Event
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]Austria
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£25
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Arrange meet
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Sold 1000 units
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Belgium
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£63
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Supply goods
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Visit complete
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Canada
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£564
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Supply goods
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Visit complete
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Denmark
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£34
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Shut down
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Shut down
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Egypt
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£67
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]Austria
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£42
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Belgium
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£40
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Canada
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£32
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Rugby
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Lunch
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Denmark
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£56
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Egypt
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£40
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: transparent"]Sheet 2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]B1
[/TD]
[TD="bgcolor: transparent"]B2
[/TD]
[TD="bgcolor: transparent"]B3
[/TD]
[TD="bgcolor: transparent"]B4
[/TD]
[TD="bgcolor: transparent"]B5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Austria
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Belgium
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Canada
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Denmark
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Egypt
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]63
[/TD]
[TD="bgcolor: transparent, align: right"]564
[/TD]
[TD="bgcolor: transparent, align: right"]34
[/TD]
[TD="bgcolor: transparent, align: right"]67
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]32
[/TD]
[TD="bgcolor: transparent, align: right"]56
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Charlie Inc
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent, align: right"]29
[/TD]
[TD="bgcolor: transparent, align: right"]45
[/TD]
[TD="bgcolor: transparent, align: right"]61
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Delta Inc
[/TD]
[TD="bgcolor: transparent, align: right"]33
[/TD]
[TD="bgcolor: transparent, align: right"]568
[/TD]
[TD="bgcolor: transparent, align: right"]98
[/TD]
[TD="bgcolor: transparent, align: right"]52
[/TD]
[TD="bgcolor: transparent, align: right"]231
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Echo Inc
[/TD]
[TD="bgcolor: transparent, align: right"]97
[/TD]
[TD="bgcolor: transparent, align: right"]238
[/TD]
[TD="bgcolor: transparent, align: right"]56
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]47
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
Try this, for your basic requirements:-
Results sheet1.

Code: 
[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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
575
madi1004
M
C
  • Question Question
Transposing Data from two columns with a common data identifier into another column one row at a time
Replies
4
Views
839
Fluff
Fluff
N
  • Question Question
Join data from second worksheet with delimited list values (mults) to another worksheet with one value per cell/row and keep 1:1 relationship
Replies
2
Views
1K
HeadAgainstABrickWall
H
S
  • Question Question
Help with VB code to copy some values from one sheet to another
Replies
0
Views
958
skeeeter56
S
R
  • Question Question
Complex or Deadly Difficult/Impossible??? : Multiple argument lookups/matches between 2 sheets
Replies
7
Views
797
Marcelo Branco
M

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top