PDA

View Full Version : [SOLVED] Create a Named range for each Unique Value in Column



simora
11-08-2015, 02:54 PM
From the Attached worksheet TransposeTest.xls:

I want to create a Named range for each Unique Value in Column A on A new worksheet.("Sheet2")
Each entity should be formatted witht the values from the Sheet1

On Sheet2 I extracted the Unique Values from Column A and transposed them, but
How do I Add the 4 Blank Columns to the right of each Name and populate them with the data from sheet1
I prefer to do this using VBA.

p45cal
11-08-2015, 04:36 PM
What are you aiming at doing? How can data from the 7 Bradley entries be contained in 4 columns? What data anyway?

SamT
11-08-2015, 05:24 PM
I want to create a Named range for each Unique Value in Column A on A new worksheet.("Sheet2")
Each entity should be formatted with the values from the Sheet1
That statement makes no sense at all to me and your example on sheet2 doesn't even fit any of the parts of that statement.

Try again, please.

simora
11-08-2015, 06:09 PM
My mistake: Sorry about that.

The wrong version of the worksheet was attached. See Worksheet TransposeTest2.xls
See Sheet3 Column Q to see a sample of the outcome that I'm aiming for.
The Company Name and the next 4 columns is the named Range,
and it can get its name from the associated Company Name.

p45cal
11-08-2015, 07:12 PM
see attached, click the button, check comments in code.

simora
11-08-2015, 08:26 PM
p45cal : (http://www.vbaexpress.com/forum/member.php?3494-p45cal)

http://www.vbaexpress.com/forum/images/statusicon/user-online.png
Thanks a Million. Worked like a charm. Really appreciate you help.

snb
11-09-2015, 01:29 AM
or


Sub M_snb()
Sheet1.Columns(1).AdvancedFilter 2, , Sheet1.Cells(1, 10), True

With Sheets.Add
For j = 2 To Sheet1.Columns(10).CurrentRegion.Rows.Count
Sheet1.Cells(2, 10) = Sheet1.Cells(j, 10)
Sheet1.Cells(1).CurrentRegion.AdvancedFilter 2, Sheet1.Range("J1:J2"), .Cells(1, 5 * j + 13)
Next
End With
End Sub

simora
11-09-2015, 12:43 PM
snb (http://www.vbaexpress.com/forum/member.php?44644-snb)
http://www.vbaexpress.com/forum/images/statusicon/user-offline.png
Thanks.

That works also, but without the formatting of the columns etc.. etc. like the other solution. Great to have an alternative process.