|
MeraWindows Forums - Get your Computer Problems Fixed |
| This is a site run by the community - for the community. At the site, we share and contribute our computer learnings with each other. We discuss about Windows, Office, Mobile, Gaming and other computer related topics. At our forums you can post your queries and our very helpful community members will respond quickly to your queries. We organize lots of competitions from time to time and award prizes to the winners.
You will need to register at the site before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.
|
|
 | | |
| | |
| | |
| | |
 | | |
 |
|
|
 |
|
Joined: 10/22/2009
Posts: 189
|
|
|
Hi All,
I am receiving data in below format and receiving on daily basis, and my problem is that i have to arrange then in continious sequence , for example:-
i received this series in below format :- 535582-535585, 535587-535589,
Now I need out put in below format :-
535582
535583
535584
535585
535587
535588
535589
and so on continue......
535582-535585, 535587-535589, 535591-535597, 535599-535605,535607, 535609, 535611, 535612-535614, 535616, 535618, 535619-535621, 535624-535637, 535640-535649, 535652, 535653-535654, 535656-535667, 535670-535672, 535674,535675-535680, 535682-535685, 535687-535696, 535698, 535700, 535702-535714, 535716,535718-535745, 535747-535772, 535775, 535776-535800, 535802, 535803-535807, 535809-535822, 535824-535829
Note:- "-" sign indicates range continious serier of number and "," indicates their is gap in the series and next number starts from the number which is just continue from
Pls asssit is their any macro or easy solution for the above.
Bregds,
Rajender
|
|
|
|
| |
|
|
|
Hi,
Is this all in one cell - 535582-535585, 535587-535589, 535591-535597, 535599-535605,535607, 535609, 535611, 535612-535614, 535616, 535618, 535619-535621, 535624-535637, 535640-535649, 535652, 535653-535654, 535656-535667, 535670-535672, 535674,535675-535680, 535682-535685, 535687-535696, 535698, 535700, 535702-535714, 535716,535718-535745, 535747-535772, 535775, 535776-535800, 535802, 535803-535807, 535809-535822, 535824-535829
OR
is this data in different cells i.e. 535582-535585 in A2, then 535587-535589 in A3 and so on
Please clarify
|
|
|
|
| |
|
|
|
|
Joined: 10/22/2009
Posts: 189
|
|
|
Hi Ashish,
All the seriers I have to copy from email and have to paste in rough sheet in 1 cell like A1 and then arrange it as per the sequence and than paste it to my master Sheet.
and series of the numbers can be different every time like in this series is 535582-535585 may be next time its 646675-647709.
Bregds,
Rajender.
|
|
|
|
| |
|
|
 |
|
Joined: 12/28/2010
Posts: 9
|
|
|
Hi
Rajender
Dats Pretty simple....
But first could u pls comform me dat hav I understood ur query ...
This is how u wanted ur output as??????
|
535582-535585
|
|
535587-535589
|
|
535591-535597
|
|
535599-535605
|
|
535607
|
|
535609
|
|
535611
|
|
535612-535614
|
|
535616
|
|
535618
|
|
535619-535621
|
|
535624-535637
|
|
535640-535649
|
|
535652
|
|
535653-535654
|
|
535656-535667
|
|
535670-535672
|
|
535674
|
|
535675-535680
|
|
535682-535685
|
|
535687-535696
|
|
535698
|
|
535700
|
|
535702-535714
|
|
535716
|
|
535718-535745
|
|
535747-535772
|
|
535775
|
|
535776-535800
|
|
535802
|
|
535803-535807
|
|
535809-535822
|
|
535824-535829
|
Regards
Sandhya T Ram
|
|
|
|
| |
|
|
|
|
Joined: 10/22/2009
Posts: 189
|
|
|
Hi Sandhya,
No, I want my data in below sequence. with running series,
|
535582
|
|
535583
|
|
535584
|
|
535585
535587
535588
|
and so on,.......
Bregds,
Rajender
|
|
|
|
| |
|
|
|
Hi,
This problem has to solved in two parts.
Part I
We will split the comma seperated values into multiple rows (effectively a text to rows operation). After the part I operations are performed, the output will be as follows:
535582-535585 in cell A4
535587-535589 in cell A5
535591-535596 in cell A6
so on
Cell A3 will have the heading. I am yet to formulate this solution
Part II
Afer we have created these individual groups, the following macro will create entries in individual cells starting from cell B4. Please input some heading in cell B3
Sub generate_series()
Dim before As String
Dim after As String
Dim seperator As Integer
Dim length As Integer
Dim entries As Long
Dim i As Integer
Dim row_count As Long
For entries = 1 To Range("A3").End(xlDown).Row - 3
seperator = Application.WorksheetFunction.Search("-", Range("A" & entries + 3), 1)
length = Len(Range("A" & entries + 3))
before = left(Range("A" & entries + 3), seperator - 1)
after = Right(Range("A" & entries + 3), length - seperator)
For i = 1 To (after - before) + 1
row_count = Cells(Rows.Count, "B").End(xlUp).Row + 1
Range("B" & row_count).Value = before
before = before + 1
Next i
Next entries
End Sub
Paste this code in the general module and then run it (Alt+F8)
As mentioned earlier, i am yet to formulate the solution for Part I. To test my code, enter the following
535582-535585 in cell A4
535587-535589 in cell A5
535591-535596 in cell A6
In cell A3 and B3 enter a heading. Now run the macro.
|
|
|
|
| |
|
|
|
|
Joined: 10/22/2009
Posts: 189
|
|
|
Hi Ashish,
Thanks a lot, but its showing error 400 in front of cells where singe number is written,( 535690-535715, 535720(error showing here), 535722-535750)
and its much time savingfor me if a macro inked with only 1 cell( not to create group in A1, A2 , A3.....) and series of the same comes according to the same.
regds,
Rajender
|
|
|
|
| |
|
|
|
Hi,
This code will take care of the error. Please try and let me know
Sub generate_series()
Dim before As String
Dim after As String
Dim seperator As Integer
Dim length As Integer
Dim entries As Long
Dim i As Integer
Dim row_count As Long
For entries = 1 To Range("A3").End(xlDown).Row - 3
If InStr(Range("A" & entries + 3), "-") = 0 Then
before = left(Range("A" & entries + 3), Len(Range("A" & entries + 3)))
after = before
Else
seperator = WorksheetFunction.Search("-", Range("A" & entries + 3), 1)
length = Len(Range("A" & entries + 3))
before = left(Range("A" & entries + 3), seperator - 1)
after = Right(Range("A" & entries + 3), length - seperator)
End If
For i = 1 To (after - before) + 1
row_count = Cells(Rows.Count, "B").End(xlUp).Row + 1
Range("B" & row_count).Value = before
before = before + 1
Next i
Next entries
End Sub
|
|
|
|
| |
|
|
|
Hi,
As mentioned in my previous code, Part I has to create individual comma seperated groups into different cells of one column. Assume your data is in cell D1. Date would be like this in cell D1
535582-535585,535587-535589,535591-535596,535598-535605,535608
Paste the following code in the General module
Sub split_cell_into_rows()
Dim varItm As Variant
Dim rngText As Range
Dim rngCl As Range
Dim i As Integer
Set rngText = Range("D1 " & Range("D" & Rows.Count).End(xlUp).Row)
For Each rngCl In rngText
arrText = Split(rngCl, ",")
i = 4
For Each varItm In arrText
Cells(i, 1) = varItm
i = i + 1
Next varItm
Next rngCl
End Sub
When you run this code, you will notice that comma seperated values in cell D1 will be created in different cells starting from cell A4. Type a heading in cell A3 and copy the same heading to cell B3. Now run the code shared with you in my previous post.
These two codes should get you what you want.
|
|
|
|
| |
|
|
|
 |
Joined: 11/8/2010
Posts: 14
|
|
|
Try this macro which will write the sequence to Sheet2....
Sub MyMacro()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
.Offset(1).Value = Val(Trim(Split(Range("A" & lngRow), "-")(0)))
If InStr(Range("A" & lngRow), "-") Then
.Offset(1).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Step:=1, Stop:=Val(Trim(Split(Range("A" & lngRow), "-")(1)))
End If
End With
Next
End Sub
|
|
|
|
| |
|
|
|
|
Joined: 10/22/2009
Posts: 189
|
|
|
Hi Ashish,
I paste the codes in module. but now below error messages is appearing:-
In generate Series :- Type Mismatch.
and In split cell:- Error 1004
and pls suggest also where and how to save the same macro's , So that Whatever file I open, i can find the same macro's in that file and their is no need to open specipically this file always.
I am using Excel 2007.
Bregds,
Rajender
|
|
|
|
| |
|
|
|
Hi,
These codes are workign fine for me. Upload the file at www.merawindows.com. The file upload/download feature is now working in this forum so please post the file here and not at www.mediafire.com.
|
|
|
|
| |
|
|
|
|
Joined: 10/22/2009
Posts: 189
|
|
|
Hi Ashish,
Sample file is uploaded.
Bregds,
Rajender
|
|
|
|
|
| |
|
|
|
Hi,
Please find attached the file with the solution. First run the split_cell_into_rows macro and then run the generate_series macro.
I have tested this on your data and it is running fine.
Do let me know how this works.
|
|
|
|
|
| |
|
|
|
|
Joined: 10/22/2009
Posts: 189
|
|
|
Many Many thanks Ashish, Its really sav my lots of time.
and Wish you and your Family a very HAPPY and PROSPEROUS NEW YEAR.
one more question whenever I have to generate this type of series I have to open this file or it can also run when this work boo closed.
Bregds,
Rajender
|
|
|
|
| |
|
|
|
Hi,
You are welcome - thank you for your feedback. A very Happy New Year to you and your family as well.
To make this macro available in any worbook you open, please save the macro in the Personal Macro workbook in the XL start folder. Please google for this on your own. This is one link you may go through
http://excelhints.com/2010/11/29/how-to-use-your-personal-macro-workbook/
|
|
|
|
| |
|
|
|
|
Joined: 10/22/2009
Posts: 189
|
|
|
Hi Ashish,
Refer to the solution provided by you earlier. I need some more assistance in this
1) I need the Sr numbers also along with the respective PO's in coloumn F.
2) I found and error messages which I have data in attached formate in coloumn B.
3) In continuation to Solution Pls ignore the MLS PO 642460(2)(in this it is treated as 642460)
4) Pls ignore all alphabetical figure in coloumn B.
Pls suggest If this is possible.
Thanks
Rajender
|
|
|
|
|
| |
|
|
|
Hi,
Please find attached the file. Run the macro named split_data_into_rows.
I do not understand points 3 and 4 below.
Hope this helps.
|
|
|
|
|
| |
|
|
|
|
Joined: 10/22/2009
Posts: 189
|
|
|
Hi Ashish,
Thanks a lot. Its working fine.
Regarding Point 3) I want to say that in many cell in coloum B Number series are like 479555/EPIU or 479555(6). and in solution I want only 479555 as output
means ignore the alphabetic value or values in Bracket"()".
2nd one in some cell Series are like 479555-70 or 479590-620 and in output it also needs to be shows as in series for example:-
479555
479556
479557
and so on .....
Bregds,
Rajender
|
|
|
|
|
| |
|
|
|
Hi,
Please find attached the workbook. The work is still incomplete. In this file, i have only focussed on extracting the purchase orders, not the sales orders.
Check the output of columns E and G thoroughly
Do not bother about my macros or codes right now.
|
|
|
|
|
| |
| | |
| | |
| | |
|
| |
|
| |
|
|
User Log In
|
 |
|
| |
|
|
Follow Us
|
|
|
|