Thread: Making new worksheets in same workbook based on unique value in parent sheet

Dear All,

i have a input sheet in a workbook with various columns and million rows

The column numbers are not fixed and nor is row length ...i want macro to handle this using rowcount and columncount

The data has a column E titled "Unit involved ".....basically each entry in this column contains the unit involved as unique identity ,like -sample 1 ,sample 2 ,sample 3 etc .....in a random manner ...approximately 150,000 entries per sheet or more ......

Now i want the macro to copy all data related to sample 1 in a new worksheet ,sample 2 in a new worksheet ,sample 3 in a new worksheet etc tll all samples from the column E gets over and we should get seperate worksheetstitled sample 1,sample 2 ,sample 3 , likewise in the same workbook .....

The first 2 rows should remain same like in input sheet ,the copying should start from 3rd row for each of the new worksheets and continue uptil the number of entries it contains in parent input sheet ,...for each of the entries all rows and columns should get copied ....i would prefer the macro to handle the column count for random sheets have random columns


Say sheet 1 (parent sheet ) has in column E ->50 entries of sample 1 ,70 sample 2 ,80 sample 3 ,90 sample 4 and 100 sample 5 entries all mixed together ,then i would want sheet 2 to be titled sample 1 containing 50 entries of sample 1 ,for each of the 50 entries ,i want all columns and data like in input sheet copied into it .....,sheet 2 to be titled sample 2 and containing 70 entries ,for each of the 700 entries ,i want all columns and data like in input sheet copied into it ..... and likewise for sheet 3 and onwardds..process to continue till all sample are covered from input sheet ...
in each of the sheets,the first 2 rows remain identical to unit sheet for title stuff ,


Would appreciate a expert help ,also trying to solve it myself ,if hit upon a solution will post it

My attached sheet may give a rough idea but the row numbers and column numbers are really not accurate in this dummy data

Thanks in advance ,


I have also raised my query at http://www.ozgrid.com/forum/showthre...goto=newpost**


Humble Regards ,


Amlan Dutta

Firstly ,thanks a billion to you both for giving your valuable time to help solving my query .Mrice ,i will do your code and get back .That's a very sleek observation ....actually this is a dummy data set ...the real one have city names ,but still you are correct when you observe that minor thing ,


Jaslake ,thanks a tonne for this macro ,i need to test it ,

I will test both macros and get back with results ,



Humble Regards ,


Amlan Dutta

Dear Jaslake & Mrice ,

I have attached the output results for both your macros separately in the attachment ,

Jaslake -Your macro results are more correct ,i have modified the code to point it to "Sheet1 " ....since my real data had Sheet 1 titled as sheet 1 and when i ran your macro ,i realised that had to account for thsi minor change .....But more importantly ,if you note in your output sheet attached .......sheets get generated twice ...
Each of the sheets are correct ,but only thing is that they get generated twice for my input data (attached)....

You will note that in output sheet ,i have loaded both macros Mrice as well as yours and named it also based on your names ,i have assigned your macro to a button and give it your name ,please press that only because right above that is Mrice macro assigned to a button ,if you press that you will get his results ,
Once you do press your macro ,you will realise what i am talking of ....also i see that row 2 is not getting copied ...row 1 is itself getting copied to row 2 ...results are accurate ..however the output sheet seems to be so big ...is there a way that the look and feel of the input sheet is retained in the output sheet....

I note that zoom resolution for input sheet is 62% and for output sheet is 100 %,maybe thats why the big big effect ,but that's just a wild guess ...is it possible that whatever be the formatting in input sheet including screen zoom percent ,all that be retained in output sheet ........

But ,forget all this -I am so so so thankful for thsi macro since if i remove the above points ,it has solved the purpose ....


I just want to say one thing ,sample 1 and sample 1 with space are the same thing ......is there a way for the macro to handle this ...that space sometimes get generated by mistake .......



2.)Mrice-Find output sheet using your macro attached .....i note a few problems here .....if you yourself see the input sheet and the output sheets ,you will see that not all entries get generated for the sample in same sheet and its broken in two sheets .....when i ran for my earlier sent sheet in the attachment ,it worked perfect but in the attached sheet which resembles more closely my real data ,it somehow doesnt capture all data of a sample data in one sheet ,

Again ,i would like to point out that spaces are a mistake ,is there a way to handle this ...i mean sample 1 and sample1 is the same .....

But in the attached sheet ,i took care to remove such spaces ,yet the macro gives me splitted sample data set in output sheet ,

I have assigned your macro to a button ,if you press that you will see for yourself the results which get generated ....the 2nd row fails to copy ......is it possible that screen look and feel of the input sheet be retained in the output sheet cause i sense a disturbance in the fact that everything looks big big !!!!!

But forget all this,tonnes of heartfelt thanks for giving me the time ,so so so soooooooooooooo much appreciated ,

Hug's,


Amlan Dutta

P.s -I have loaded both macros in same sheet ,infact each of you can use the other's macro to see how they fare .......i have renamed the module after your first names so that there is no confusion ...also in macro button ,i have named it based on your names .....so that you can press your as well as each others to just note the performance .......

Hiya,

Me again...sorry for the bugging requests.....Is it also possible to retain column width and row width in output sheet exactly like in Input sheet ,you will see from that attached output sheet ,that it gets generated in default excel column width and row width ...since i have column lot of text in A,B,C, etc ,i make this columns purposedly a little big .....now when the new sheets get generated ,i see that all my formatting changes as in column width ,row width ,cell wrapping in input sheet dissappear...is there a way to retain this while the output sheets get generated .....i won't mind it taking a little time since the sheets have to be presented to upper board who crib about it looking non-organised....i know it's wishing for too much ,but i would really love it in case it's possible ........


Thanks a tonne ,


Humble Regards ,


Amlan Dutta

Hi Amlan

Interesting problem so I also wanted to have a go at it.

Fixed trailing space, copied format from "INPUT" sheet to the other sheets and sort sheets as well i.e. sheet "Sample1" gets placed before "Sample2" and so forth.

But both jaslaks and mrices codes runs faster than mine so if you have a lot of data as you say perhaps it will be better take one of their solutions.

Question in cells H3 and down you have a RANDBETWEEN function. So every time I press "Delete" the values in column H ("final price") changes and column I as well since the formula in I is H * 0,003

I'm just curious of this to my mind a haphazard way to set a price. Would you care to elaborate a bit on this perhaps?

Alf

Hi Alf ,

Thanks a tonne ,buddy for your time in attempting to answer my question,it means a lot to me and i appreciate it a lot ....regards time ,i will integrate a timer to each of yours.mrice and jaslake code and get values pasted in A1,A2,A3 cell so that we can actually note the time performance (just for knowledge )......


Coming to your question of price fixation ,must say that you hit the nail on the head when you see that there is no sense to price fixation ....i used randbetween function to generate odd numbers for every column to make it dummy invalid data to not risk my company credentials for obvious sensitivity issues .....

But the fact that you ask ,here's how it is done at my firm ,

We have a basic price ,say 100$
next column is freight @3% of basic price =3$
next is transit insurance @0.3% of basic price =0.3$
Next in india ,we have to pay excise duty (tax)+education tax @10.3% of basic price =10.3$
Also ,we pay sales tax @2%(basic price + excise )=@2%(110.3)=2.206$

Finally final price/per unit is summation of all this = 100+3+0.3+10.3+2.206=115.806 $ per unit ,


So if customer wants 10 units ,the price becomes =115.8*10=1158 $


That's how it happens in india ,

You see ,this was not important for me for i wanted to create seperate sample sheets so i kept random data here and did some odd garbage because i wanted to make it look as unreal as possible lest someone ever sees this info ........But if you would be really interested in having a feel of how price is calculated ,i can obviously do a dummy sheet for you ,but i think my calculation explanation above pretty much explains price fixation in india .........

Take good care ,buddy ,

I remain a passionate student of VBA ,also i had VOG a excelMVP answering for me for the same problem by his own code which goes like this ,

Sub sweetmacro()
Dim LastRow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(3, 1), .Cells(LastRow, LastCol)).Sort Key1:=.Range("E3"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 3
    For i = 3 To LastRow
        If .Range("E" & i).Value <> .Range("E" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("E" & iStart).Value
            On Error GoTo 0
            ws.Range(Cells(1, 1), Cells(2, LastCol)).Value = .Range(.Cells(1, 1), .Cells(2, LastCol)).Value
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A3")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

i somehow liked this code most since as a beginner ,this is far easy to decipher and understand but somehow it throws up a range error ,can you figure out why the above code doesn't work .i will be immensely greatful ,
And if you do ,then i will land up having 4 answers to my solution ....like in maths ,i love having so many solutions to a single query ,it's a fantastic feeling ,


I attach it as well so that you can directly run and see that it throws up a error ,in the meantime let me try your code and get back ,


Thanks again ,



Humble Regards ,


Amlan Dutta

Dear Alf ,Jaslake and Mrice ,


I have attached a very real sheet (alf ,you can actually connect to this sheet to understand )how prices are calculated at my firm for determining final price ....I have just made one modification ,in column of basic works i have used randbetween function to populate cells ,rest i have let actual formulas we actually use so that you can know how exactly things happen at our place ,


Anyways coming back to actual point ,all the 3 macros are hereby attached in this sheet ,i request that each of you please go through the sheet ,i will shortly synopsise what i observe ,

Alf - Results unfortunately come wrong ,formatting perfect and beautiful ,wish results were correct ,
.For each samplein co lumn E, new sheet definitely is created but in that sample sheet ,all other samples unconcerned to that sample and present in original column E are also there ,

Let me explain -say i have sample 1 ,sampl 2 ,sample 3 ...you will note that in each of your sheet sample 1 ,we have sample 1,2,3 data ,for sample 1 sheet we should have only sample 1 data .....otherwise i love the look and feel of output sheet ,
Strangely i find your macro code very sweet ,simple and easy to understand and in fact not slow as you think ....it worked superfast over the data i ran ,


Alf ,amy dying to see your code work honestly ,since i am in love with the formatting ,look and feel etc in the output sheets ....there would be nothing better than this ,please give it thought ....thanks inn advance

Jaslake -Only your results are perfect ,but second row doesn't get copied ...for each sample ,2 sheets get generated ,remaining issues like in trailing mail communicated ,rest it is superfast and at present the only working solution ,so i am highly greatful


Mrice -Results are incorrect ,sample 1 etc data gets split and distributed across sheets ..it would be nice to have formatting of input sheet retained in output sheet


To make matters simple ,i have attached a sheet titled "God "in which i have assigned macro buttons Mrice macro ,jaslake macro ,alf macro to each of your modules so that you can press it and see the output generated yourself to better appreciate the above points ,

Obviously ,if you press other persons button ,then you will see his output ,so preferable is to press your won button ,

The attached sheet is very much like the sheet i operate everyday ,


Humble Regards ,


Amlan Dutta

Hi Amlan

Thank for feed back on the pricing issue. Not a bad idea using the "RANDBETWEEN" to generate different numbers.

Re macro I see that in your new file you have extended the data range from previous columns A to I, to A to column P.

So you need to edit my macro a bit. Line

.Range("A2:I2").AutoFilter

should be changed to

.Range("A2:P2").AutoFilter

and line

Range("A2:I2").AutoFilter

should now be

Range("A2:P2").AutoFilter

I assume that column E is still the column used to generate all different sheets. By the way there are trailing spaces in some of the names. Are you testing us or?


Alf

Alf,

The output your macro generates is wrong ,i think i am failing to make you explain ,

Here ,your output sheet

You will note that sample sheets generated by your macro are rightly based on unique identities in column E (which for the attached example is )->Trichy ,ranipet ,CBU ,HERP ...so this sheets should get generated which it does ,however this sheets should contain data specific to only this fields which is not the case ..

What your macro does is make correctly sheets-> trichy ,ranipet ,CBU ,HERP correctly ,but in trichy sheet ,entries of ranipet ,herp ,cbu are also present which is wrong ....

I attach your output sheet (Time taken =3.5 seconds )so that you can see what is going wrong ,


i also attach jaslake sheet to help you understand what i wish in terms of results .....


Alf ,i fail to see the trailing spaces ...nope ,i am not testing yourl ,infact i took pain to remove all of them from column E ,yes data to be sorted will always be in column E,

Wish your macro works ,as of now results are wrong ,



Besides ,i did integrate a timer to your script and Jaslake's .....his time =1.438 seconds is pretty fast ,

Thanks a tonne ,wish your macro could work ,it would be really nice ,


Humble Regards ,



Amlan Dutta

Hi

Saw the sheet you mailed me and yes data all mixed up. But when I do a rerun everything is ok. I can't see any problem. Returning file after I did a rerun. This is what I see i.e. sheet CBU only contains cbu data and sheet HERP only contains HERP data and so forth. Is there something I'm missing??

If you like to test you could try to step through my macro and see if you cand find something that does not work in your environment.

Alf

Dear Alf ,

I saw your sheet ,it's perfect and lovely but when i do a rerun at my place ,it makes it back to unsorted data i had earlier send you ,this way it doesn't solve the purpose for i can never use the macro (((

...when you say environment ,what exactly does that mean ,i am using excel 2007 now ....and i guess the environment remains same in both our systems ....i have enever encountered such a problem so i am clueless as to how to explain ...i think at your place the code works perfectly ,but at my place ,when i run it ,it gives the result like i mailed earlier ,i did step through but sadly as i saw the code execute ti saw unsorted data being generated step by steo as it proceeded .......


In fact your own sheet which is so perfect ,when i ran your macro in it ,it again ended up being what i had earlier ....i feel terribly sad for i love what you have in your screen using the macro but at my place it is behaving very naughtily ,what puzzles me is how can the same line of code work so beautifully at your place and generate different results at my place -is this logically possible,

i attach the results after i run your macro on the file you send ,

Any idea as to how to solve this or does this puzzle you as much as it puzzles me ???

Humble Regards ,


Amlan Dutta

I’m really puzzled by this “behavior” and the only thing I suspect is that the autofilter don’t work the same way on your PC as on mine even if both of us have Excel 2007.

The copied data result you get on the sheets “CBU”, “HERP”, “ranipet” and “Trichy” is
the same as the data on sheet “INPUT” and this could only be so if filtering and copying don't work.

I’ve modified the macro a bit replacing

Activesheet.UsedRange.Offset(2).Copy

with

Set Rng = ActiveSheet.AutoFilter.Range
   Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Copy

as I think this is the part most likely to cause this problem.

If this doe’s not solve your problem you could try to step through my macro and see what goes wrong and where.

Other possible causes could be that “Autofilter” is not set by macro on range A2 to P2
or that no filtering is done but I don’t think that is so likely as it’s the copy parts that fails.

Oh bugger! can't upload my file as there is problem with the server!!

I'll just paste the code here so you can copy it.

Option Explicit

Sub Split_data()

Dim wsDest As Worksheet
Dim ShName As String
Dim cell As Range
Dim Rng As Range
Dim i As Integer
Dim j As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

For Each wsDest In Worksheets

If wsDest.Name <> "INPUT" Then
    wsDest.Delete
End If

Next

Sheets("INPUT").Copy After:=Sheets(1)
ActiveSheet.Name = "Templ"
ActiveSheet.UsedRange.Offset(2).ClearContents

Set wsDest = Worksheets.Add(Before:=Sheets("INPUT"))
wsDest.Name = "Analyse"

Range("INPUT!E3:E" & Range("INPUT!E" & Rows.Count).End(xlUp).Row).Copy
Range("A2").PasteSpecial Paste:=xlPasteValues

ActiveSheet.UsedRange.Sort Key1:=Range("A2"), Order1:=xlAscending
ActiveSheet.UsedRange.Name = "Raw_data"

i = 0

For Each cell In Range("Raw_data")
       cell.Value = Trim(cell.Value)
            If Application.CountIf(Columns(2), cell) = 0 Then
                Range("B2").Offset(i, 0) = cell.Value
               i = i + 1
            End If
     Next cell

Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Name = "uniqe"

For Each cell In Range("uniqe")

Sheets("Templ").Copy After:=Sheets("Templ")
ActiveSheet.Name = cell

Sheets("INPUT").Activate

With ActiveSheet

        .AutoFilterMode = False

        .Range("A2:P2").AutoFilter

End With

Selection.AutoFilter Field:=5, Criteria1:=cell.Value, Operator:=xlAnd

Set Rng = ActiveSheet.AutoFilter.Range
   Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Copy

ShName = cell

Sheets(ShName).Activate

Range("A3").PasteSpecial Paste:=xlAll

Range("A2").Activate

Next cell

Application.CutCopyMode = False

Sheets("Analyse").Delete
Sheets("Templ").Delete

For i = 2 To Sheets.Count - 1
For j = i + 1 To Sheets.Count
    If UCase(Sheets(i).Name) > UCase(Sheets(j).Name) Then
        Sheets(j).Move Before:=Sheets(i)
    End If
Next j
Next i

Sheets("INPUT").Activate
Range("A2:P2").AutoFilter

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Alf (keeping fingers crossed)