List of units per location

[rickylanders]

Hello,

I have a list of locations and a list of units. I need a way to make it so that each location has a the same list of units.

For example:
Units List: a, b, c, d, e, f

Location list: 1, 2, 3, 4, 5

Need it to look like this:
1 a
1 b
1 c
1 d
1 e
1 f
2 a
2 b
2 c
2 d
2 e
2 f
etc.

 

[hiker95]

rickylanders,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


We can not tell where your raw data is located, sheet name, cells, rows, columns, and, we can not tell where the results should be, sheet name, cells, rows, columns.

To start off, and, so that we can get it right on the first try:

Can you post a screenshot(s) of the actual raw data worksheet(s)?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Zakkaroo]

In your specific example - the following would work, you fill this in manually and then drag it down as far as you want
A1= blank

Excel 2010
	A	B
2	1	a
3	1	b
4	1	c
5	1	d
6	1	e
7	1	f
Sheet2

Cell Formulas
Range	Formula
A2	=A1+1
A3	=A2
A4	=A2
A5	=A2
A6	=A2
A7	=A2

 

[hiker95]

rickylanders,

Here is a macro solution for you to consider, that uses three arrays I memory, based on what I think your raw data, and, results may look like.

I can adjust the macro after we see your screenshots.

You can adjust the raw data worksheet name in the macro.

Sample raw data, and, results:

Excel 2007
	A	B	C	D	E	F
1	Units List	Location List			Locations	Units
2	a	1			1	a
3	b	2			1	b
4	c	3			1	c
5	d	4			1	d
6	e	5			1	e
7	f				1	f
8					2	a
9					2	b
10					2	c
11					2	d
12					2	e
13					2	f
14					3	a
15					3	b
16					3	c
17					3	d
18					3	e
19					3	f
20					4	a
21					4	b
22					4	c
23					4	d
24					4	e
25					4	f
26					5	a
27					5	b
28					5	c
29					5	d
30					5	e
31					5	f
32
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub CreateListOfUnitesPerLocation()
' hiker95, 06/24/2015, ME863685
Dim a As Variant, b As Variant, o As Variant
Dim i As Long, k As Long, j As Long
With Sheets("Sheet1")   '<-- you can change the sheet name here
  a = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
  b = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)
  ReDim o(1 To UBound(a, 1) * UBound(b, 1), 1 To 2)
  For k = 1 To UBound(b, 1)
    For i = 1 To UBound(a, 1)
      j = j + 1: o(j, 1) = b(k, 1): o(j, 2) = a(i, 1)
    Next i
  Next k
  .Columns("E:F").ClearContents
  .Range("E1").Resize(, 2) = Array("Locations", "Units")
  .Range("E2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns("E:F").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CreateListOfUnitesPerLocation macro.

 

[rickylanders]

Thank you very much, I will give this a shot.

Rich

p.s. Windows 7, Excel 2010, my data is in two sheets. List of locations is in sheet 1, list of units is in sheet two. Each location should have one of each unit. I'll work on going through the steps you outlined to add screenshots.

 

[hiker95]

rickylanders,

Thanks for the update.

I will be looking for your next reply.

 

[rickylanders]

rickylanders,

Thanks for the update.

I will be looking for your next reply.

The Macro worked PERFECTLY!! Thank you so very much. Seriously, it almost brought tears to my eyes.

 

[hiker95]

rickylanders,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.