Newbie Question: Using Excel to work with output of PI - break sets of numbers, remove numbers!

[robbertly]

Hi,

I'm a newbie here, and to excel at this level, and have a question I hope somebody can help with.

I have a list of numbers outputted from a review of PI (the number 3.1415926535897932384......)

It looks like this:

8850838032 0621312483 8327044318 1257233570 9958940293 1391776730 2923888859 5836058683 5192760238 4694561699 : 110000000001
9312900154 4838526183 9375914106 9846458403 5847003707 2451543553 9394699328 5157228504 5434270590 6509736487 : 110000000002
1284090545 3175919151 4159855781 3410862263 2549812643 7600394225 7109902021 0694219181 6542482795 7164656581 : 110000000003
1367977800 8915483236 6072599505 1466161901 1090687303 7608155585 3289637107 6490574006 0401938787 7258319674 : 110000000004

- the list is in notepad (.txt file)
- set of 10 digits per number
- set of 10 numbers per line (10 digits per x 10 numbers per line)
- reference number at the end ( the : 110000000001 : 110000000002 etc numbers)


Heres what I would like to do:

- to remove the reference numbers first ( the : 110000000001 : 110000000002 etc numbers)
- to break each set of 10 digits into 5 sets of 2 digit numbers, with each 2 digit number appearing on a seperate line of its own - 0621312483 to 06 21 31 24 83
- from this I want to remove all 2 digit numbers over 26, so that I am only left with numbers from 01 - 26 - from 06 21 31 24 83 to 06 21 24

Any ideas of how to do this in excel?

Any assistance, suggestions, ideas welcome.

Thanks

 

[hiker95]

robbertly,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?


Be back in a little while.

 

[hiker95]

robbertly,

Sample raw data:

Excel 2007
	A	B	C
1	8850838032 0621312483 8327044318 1257233570 9958940293 1391776730 2923888859 5836058683 5192760238 4694561699 : 110000000001
2	9312900154 4838526183 9375914106 9846458403 5847003707 2451543553 9394699328 5157228504 5434270590 6509736487 : 110000000002
3	1284090545 3175919151 4159855781 3410862263 2549812643 7600394225 7109902021 0694219181 6542482795 7164656581 : 110000000003
4	1367977800 8915483236 6072599505 1466161901 1090687303 7608155585 3289637107 6490574006 0401938787 7258319674 : 110000000004
5
Sheet1

After the macro (using two arrays in memory) in column C:

Excel 2007
	C
1	06
2	21
3	24
4	04
5	18
6	12
7	23
8	02
9	13
10	23
11	05
12	02
13	16
14	12
15	01
16	06
17	03
18	00
19	07
20	24
21	22
22	04
23	05
24	09
25	12
26	09
27	05
28	10
29	22
30	25
31	26
32	00
33	25
34	09
35	20
36	21
37	06
38	21
39	13
40	00
41	15
42	05
43	14
44	16
45	19
46	01
47	10
48	03
49	08
50	15
51	07
52	06
53	04
54	01
55
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 BreakSets()
' hiker95, 10/19/2014, ME812677
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim s, k As Long, n As Long
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A1:A" & lr).Value
ReDim o(1 To lr * 50, 1 To 1)
For i = 1 To lr
  s = Split(Left(a(i, 1), 109), " ")
  For k = LBound(s) To UBound(s)
    For n = 1 To 10 Step 2
      If Mid(s(k), n, 2) < 27 Then
        j = j + 1
        o(j, 1) = Mid(s(k), n, 2)
      End If
    Next n
  Next k
Next i
Columns(3).ClearContents
With Range("C1").Resize(j)
  .NumberFormat = "@"
  .Value = o
End With
Application.ScreenUpdating = True
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

Then run the BreakSets macro.

 

[hiker95]

robbertly,

With the same sample raw data in my reply #3:

After this next macro:

Excel 2007
	C
1	00
2	01
3	02
4	03
5	04
6	05
7	06
8	07
9	08
10	09
11	10
12	12
13	13
14	14
15	15
16	16
17	18
18	19
19	20
20	21
21	22
22	23
23	24
24	25
25	26
26
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).

Sub BreakSets_V2()
' hiker95, 10/19/2014, ME812677
Dim rng As Range, c As Range, o
Dim s, i As Long, n As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With CreateObject("Scripting.Dictionary")
  For Each c In rng
    s = Split(Left(c, 109), " ")
    For i = LBound(s) To UBound(s)
      For n = 1 To 10 Step 2
        If Mid(s(i), n, 2) < 27 Then
          If Not .Exists(Mid(s(i), n, 2)) Then
            .Add Mid(s(i), n, 2), Mid(s(i), n, 2)
          End If
        End If
      Next n
    Next i
  Next c
  o = Application.Transpose(Array(.Keys))
  n = .Count
End With
Columns(3).ClearContents
With Range("C1").Resize(n)
  .NumberFormat = "@"
  .Value = o
  .Sort key1:=Range("C1"), order1:=1
End With
Application.ScreenUpdating = True
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

Then run the BreakSets_V2 macro.

 

[hiker95]

robbertly,

With the same sample raw data in my reply #3:

And, after the last macro:

Excel 2007
	C	D
1	00	3
2	01	3
3	02	2
4	03	2
5	04	3
6	05	4
7	06	4
8	07	2
9	08	1
10	09	3
11	10	2
12	12	3
13	13	2
14	14	1
15	15	2
16	16	2
17	18	1
18	19	1
19	20	1
20	21	3
21	22	2
22	23	2
23	24	2
24	25	2
25	26	1
26
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).

Sub BreakSets_V3()
' hiker95, 10/19/2014, ME812677
Dim rng As Range, c As Range, o
Dim s, i As Long, n As Long
Application.ScreenUpdating = False
Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With CreateObject("Scripting.Dictionary")
  For Each c In rng
    s = Split(Left(c, 109), " ")
    For i = LBound(s) To UBound(s)
      For n = 1 To 10 Step 2
        If Mid(s(i), n, 2) < 27 Then
          If Not .Exists(Mid(s(i), n, 2)) Then
            .Add Mid(s(i), n, 2), 1
          Else
            .Item(Mid(s(i), n, 2)) = .Item(Mid(s(i), n, 2)) + 1
          End If
        End If
      Next n
    Next i
  Next c
  Range("C1").Resize(.Count).NumberFormat = "@"
  Range("C1").Resize(.Count).Value = Application.Transpose(.Keys)
  Range("D1").Resize(.Count).Value = Application.Transpose(.Items)
  Range("C1:D" & .Count).Sort key1:=Range("C1"), order1:=1
End With
Application.ScreenUpdating = True
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

Then run the BreakSets_V3 macro.

 

[robbertly]

Hi Hiker95,

thanks for getting back to me on this so quickly...

Absolutely brilliant, really impressed... your first code/macro worked perfectly.... really appreciate the effort and work on that.

Just one more question if you don't mind...

The output from the four lines of numbers was 54 numbers under 26, in a descending line of 54 numbers. I will be using a couple of thousand numbers and wonder if itwould it be possible to alter the macro to create a new column after it reaches a count of 250 numbers under 26 i.e. column C = 1-250, D = 251-500, E = 501 to 750 etc.

If thats possible.... thats my problem solved!

Many thanks,

R

 

[hiker95]

robbertly,

Thanks for the feedback.

You are very welcome. Glad I could help.

The output from the four lines of numbers was 54 numbers under 26, in a descending line of 54 numbers. I will be using a couple of thousand numbers and wonder if it would it be possible to alter the macro to create a new column after it reaches a count of 250 numbers under 26 i.e. column C = 1-250, D = 251-500, E = 501 to 750 etc.

If thats possible.... thats my problem solved!

Yes it is. Let me think about this.

 

[hiker95]

robbertly,

I duplicated your four row groups down to row 120 (not all rows are shown for brevity):

Excel 2007
	A
1	8850838032 0621312483 8327044318 1257233570 9958940293 1391776730 2923888859 5836058683 5192760238 4694561699 : 110000000001
2	9312900154 4838526183 9375914106 9846458403 5847003707 2451543553 9394699328 5157228504 5434270590 6509736487 : 110000000002
3	1284090545 3175919151 4159855781 3410862263 2549812643 7600394225 7109902021 0694219181 6542482795 7164656581 : 110000000003
4	1367977800 8915483236 6072599505 1466161901 1090687303 7608155585 3289637107 6490574006 0401938787 7258319674 : 110000000004
5	8850838032 0621312483 8327044318 1257233570 9958940293 1391776730 2923888859 5836058683 5192760238 4694561699 : 110000000001
6	9312900154 4838526183 9375914106 9846458403 5847003707 2451543553 9394699328 5157228504 5434270590 6509736487 : 110000000002
7	1284090545 3175919151 4159855781 3410862263 2549812643 7600394225 7109902021 0694219181 6542482795 7164656581 : 110000000003
8	1367977800 8915483236 6072599505 1466161901 1090687303 7608155585 3289637107 6490574006 0401938787 7258319674 : 110000000004
117	8850838032 0621312483 8327044318 1257233570 9958940293 1391776730 2923888859 5836058683 5192760238 4694561699 : 110000000001
118	9312900154 4838526183 9375914106 9846458403 5847003707 2451543553 9394699328 5157228504 5434270590 6509736487 : 110000000002
119	1284090545 3175919151 4159855781 3410862263 2549812643 7600394225 7109902021 0694219181 6542482795 7164656581 : 110000000003
120	1367977800 8915483236 6072599505 1466161901 1090687303 7608155585 3289637107 6490574006 0401938787 7258319674 : 110000000004
121
Sheet1

And, ran the new macro (using two arrays in memory), and, got this (not all rows are shown for brevity):

Excel 2007
	B	C	D	E	F	G	H	I	J
1		06	20	01	08	22	13	14
2		21	21	06	15	25	23	16
3		24	06	03	07	26	05	19
4		04	21	00	06	00	02	01
5		18	13	07	04	25	16	10
6		12	00	24	01	09	12	03
7		23	15	22	06	20	01	08
8		02	05	04	21	21	06	15
117		13	14	05	24	06	03	07
118		23	16	09	04	21	00	06
119		05	19	12	18	13	07	04
120		02	01	09	12	00	24	01
121		16	10	05	23	15	22
122		12	03	10	02	05	04
123		01	08	22	13	14	05
124		06	15	25	23	16	09
247		26	05	19	12	18	13
248		00	02	01	09	12	00
249		25	16	10	05	23	15
250		09	12	03	10	02	05
251
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).

Sub BreakSetsInto250Rows()
' hiker95, 10/20/2014, ME812677
Dim a As Variant, o(1 To 250, 1 To 1) As Variant
Dim i As Long, j As Long
Dim s, k As Long, n As Long
Dim lr As Long, lc As Long, nc As Long
Application.ScreenUpdating = False
lc = Cells(1, Columns.Count).End(xlToLeft).Column
If lc > 1 Then Columns(3).Resize(, lc - 2).ClearContents
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A1:A" & lr).Value
nc = 2
For i = 1 To lr
  s = Split(Left(a(i, 1), 109), " ")
  For k = LBound(s) To UBound(s)
    For n = 1 To 10 Step 2
      If Mid(s(k), n, 2) < 27 Then
        j = j + 1
        o(j, 1) = Mid(s(k), n, 2)
        If j = 250 Then
          nc = nc + 1
          Columns(nc).ClearContents
          With Cells(1, nc).Resize(j)
            .NumberFormat = "@"
            .Value = o
          End With
          Erase o
          j = 0
        End If
      End If
    Next n
  Next k
Next i
If j > 0 Then
  nc = nc + 1
  Columns(nc).ClearContents
  With Cells(1, nc).Resize(j)
    .NumberFormat = "@"
    .Value = o
  End With
End If
Columns(3).Resize(, nc - 2).AutoFit
Application.ScreenUpdating = True
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

Then run the BreakSetsInto250Rows macro.

 

[hiker95]

Cross Posted here:

Newbie Question: Using Excel to work with output of PI - break sets of numbers, remove num


A message to forum cross posters.

Please read this:
Excelguru Help Site - A message to forum cross posters