# Excel Problem

#### Icewynd

##### Member
I’m hoping someone can help me with an Excel problem. I have some Pick 3 data and I am trying to extract the digits 4s, 5s and 6s into a single list, arranged vertically in one column as shown below.
Where a line has 2 or 3 of the digits I would like to have Excel arrange them as
4
5
Or
4
5
6

When none of these digits appear the line should be ignored (as for 977, below)

So I want data in this format:

5 3 2.....................5
5 7 4...............4....5
4 3 3...............4
6 8 7.............................6
6 5 4..............4......5......6
9 7 7..............................
7 5 9.....................5
0 8 5.....................5

to look like this:

5
4
5
4
6
4
5
6
5
5

#### Frank

##### Member
Ive just taken a look at this and have a solution of sorts, but the question arises, what happens if there are double or treble digits ?

eg

5 3 2.....................5
5 7 4...............4....5
4 3 3...............4
6 8 7.............................6
6 5 4..............4......5......6
9 7 7..............................
7 5 9.....................5
0 8 5.....................5
5 5 5..................... ?
6 5 6..................... ?
4 0 4 ..................... ?
Do they all count, or just one per line ?

Also I'm assuming your original three digits are in separate cells ?

Thanks,
Frank

#### Icewynd

##### Member
You've hit the nail on the head, Frank. If there were no double or triple digits it wouldn't be so difficult.

For example, if the number is 542 then we have 4,5 which need to be listed as:
4
5

Same idea for a triple -- you would need 3 lines to account for the 3 digits.

Good Luck!

#### Frank

##### Member
Ah, I thought so. I think I know what to do..

#### Frank

##### Member
Hi Ice,

Well my solution uses two filtering tables and a macro. So that adds 6 columns of formulas. You will need 2 named ranges, one defining the top of your listing and the other defining where to start outputting your vertical list.

Assuming your digits start in cells A2,B2,C2 and continue below, in cell E2, paste in the formula :- =IF(OR(A2=4,A2=5,A2=6),A2,0). Copy across to F2 and G2. You can copy this row of formulas down the page. This just filters out the non 4,5,6 keeping the remainder in order.

Now the ordering table:-
In cell I2 paste in the formula:- =LARGE(\$E2:\$G2,3)
in cell J2 Paste in the formula:- =LARGE(\$E2:\$G2,2)
in cell K2 paste in the formula:- =LARGE(\$E2:\$G2,1)

You can copy this row of formulas down the page. So now all the required numbers remain in their sorted order, except for zeroes which need to be filtered out by the macro which scans this table row by row starting from the top. As it scans, it adds each digit it encounters to a string, and it does so until it reaches the bottom of the table.

So, to tell the macro where to start, you need to name the cell diagonally above and to the left above cell I3 (i.e cell H2) .."start"

Finally decide which column you want your output to appear in, and name the cell below which you wish to see your output. Name the cell "out".

Go to your VBA editor , find (or create) Module 1 and paste in this code:-

Option Explicit

Sub CollectDigits()

Dim numbers As String
Dim i, col, row, lastrow, numrows, n As Integer
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
numbers = ""

Range("start").Select ' used rows are counted below this start point in the current table region
numrows = ActiveCell.CurrentRegion.Rows.Count

For row = 1 To numrows - 1
For col = 1 To 3
n = ActiveCell.Offset(row, col).Value
If n > 0 Then
numbers = numbers & n
End If

Next
Next

Range("out").Resize(10000, 1).Select
Selection.ClearContents
Range("out").Select

For i = 1 To Len(numbers)
ActiveCell.Offset(i, 0) = Mid(numbers, i, 1)
Next
Range("out").Select

End Sub

Note 1, you only need one "Option Explicit" at the top of the module. If there is already one in there, delete the one I used.

Note 2, the macro assumes that the sheet name you are using is Sheet 1. If your sheet has another name, change the Set sht = ThisWorkbook.Worksheets("Sheet1") to reflect the actual sheet name.

Note 3. Ive assumed the column you use for output is clear of data for 10000 cells downwards, as it deletes that range every time it writes. This could be edited if too large.

Now run the macro, you will need to do this every time you add new data. ( a button wuld help).

Limitations:- It re scans the list and rewrites the whole lot every time you run the macro, so it doesn't just add new digits to bottom the list. You have control of where it begins from and where it writes to, with named ranges.
Unlike formulas you cannot easily trace back from the output which row of data matches a number in the list. Not a problem with a short list, but with a long one, only the first and last ones are easy to relate to the source.
I hope this does the trick, let me know if there are problems,

Good Luck, Frank

#### Icewynd

##### Member
Thanks, Frank. I will give it a whirl.

I'm doing this spreadsheet to automate a friend's pencil and paper system, but as I understand it, there is no need to associate the vertical list back to the actual data. I think he only looks at the last few items in the list.

Good Luck!

#### Frank

##### Member
Errata:- you probably spotted that the range ("start") should in fact be diagonally above and to the left above cell I2 (i.e cell H1) . Sorry about that, I had 2 versions of solutions and read back from the wrong one regarding the address of "start".

#### Icewynd

##### Member
Frank,

Works like a charm and does exactly what I needed.

Sincere thanks,

#### Frank

##### Member
I'm glad to be of help.