Help needed reducing list.

  • Thread starter Thread starter Chris Mitchell
  • Start date Start date
C

Chris Mitchell

I have a list of numbers, some are 5 digits the others are 6 digits long.

For some of the 6 digit numbers there is a complete set, i.e. XXXXX0 through
to XXXXX9. Where this is the case I want to replace the existing 10 entries
with a single 5 digit number i.e. XXXXX. Where there is an incomplete set
of 6 digit numbers, then I want to leave the existing 6 digit numbers in the
list.

I also want all existing 5 digit numbers to be included in the finished
list.

How can I get excel to do this?

TIA.

Chris.
 
Sort the numbers in ascending order. Assuming you have a header row so
that the numbers start in A2, put this in B2:

=LEFT(A2,5)

and copy down, then put this in C2:

=IF(LEFT(A2,5)=LEFT(A1,5),"",COUNTIF(B:B,B2))

and copy down. This should give you the totals for each leading 5-
digits, so you can scan through looking for 10 (or 11, as you might
also have just the 5-digit part) and then manually replace the block
of 10 with a single row.

You can delete columns B and C when you've finished.

Hope this helps.

Pete
 
Insert a new worksheet named "result" and run this macro! The macro supposes
your data being in range("A1:An") and this is the active sheet:

Sub test()
Dim currnum As Range
Set currnum = Range("A1")
rescount = 0
Do While Not IsEmpty(currnum)
rescount = rescount + 1
If Len(currnum) < 5 Or Len(currnum) > 6 Then
MsgBox currnum.Value, vbOKOnly, "Invalid length!"
Else
If Len(currnum) = 6 And currnum.Value + 9 = Range("A" &
currnum.Row + 9) Then
Worksheets("result").Range("A" & rescount).Value =
currnum.Value / 10
Set currnum = Range("A" & currnum.Row + 10)
Else
Worksheets("result").Range("A" & rescount).Value =
currnum.Value
Set currnum = Range("A" & currnum.Row + 1)
End If
End If
Loop
End Sub

Regards,
Stefi

„Chris Mitchell†ezt írta:
 
Thanks Stefi.

Did as you suggested but all it did was to lock PC, had to Ctrl + alt +
delete to end excel.

Macros are another thing that I'm not familiar with so I probably did
something wrong.

I added a new sheet result, and created the Macro in the sheet that contains
the data by copying and pasting your text and tidied up to get rid of red
warning text in VB, i.e. take out line wraps until text was all normal
(black). Then ran the macro, but no joy.
 
Sorry, forgot to mention that you should place the macro in a normal module
(not a sheet module):
Create sheet named "result"
make sure that your data sheet is the active sheet
Open VBE (Alt+F11)
right click on your workbook name
choose Insert>Module from the local menu
Paste code in the Module code window
Place cursor between Sub and End Sub lines
Run>Run Sub (F5)

Stefi


„Chris Mitchell†ezt írta:
 
Thanks Stefi.

Worked OK this time after I tweaked it to allow for some unexpected 7 digit
numbers.

Thanks again.
 
Back
Top