converting rows of numbers to one column

[dwrowe001]

Hi everyone,

I would like to take strings of numbers from one sheet arranged in Rows and put them all into one column on another sheet. Eliminating any duplicates, zeros and no numbers above 60.
So I have numbers arranged as follows:
D12:R12,
D14:R14
D16:R16
D18:R18
D20:R20
D32:M32
D33:K33
D34:I34
In the above strings of numbers there could be for example in D12:R12, only 3 numbers in D12, E12, F12, and then Zeros in the rest of the cells to R12. For example, the numbers could be 03, 15, 45. numbers could range from 01 up to 99. and there could zeros as "00"

For example, In the row D14:R14, there could be numbers all the way to Q14 and 00 in R14.

Also, in the above strings there could be duplicate numbers either in the same string or in one or two of the other rows.

I would like to take all the above numbers from the rows and put them all in one continuous column on another sheet, eliminating any duplicates and zeros and numbers above 60…

So, for the first row D12:R12, the 3 numbers listed in the above example would be the first three numbers in the column the other sheet, all dups and zeros and numbers above 60removed. Then right under those three numbers would be the numbers from D14:R14…. And so on.

Thank you
Dave

 

[dwrowe001]

A few clarifications:
1. You are looking for a macro, not a formula?
2. Regarding "00", is that (and all the other numbers in the ranges) a text string "00" or does the cell contain the number 0 and formatted with a custom format to show the 2 zeros?

Hi Peter,
well, I was hoping for a Formula... if at all possible. I do realize, even being the neophyte at Excel that I am, that my request my be to much for a formula to accomplish. Having said this, I know even less about Macros?? I don't know how to implement this macro.

yes, the cells contain numbers, and they cells are formatted as custom "00" to take 2 zeros.

Thanks for the help with this.

Dave

 

[Rick Rothstein]

Having said this, I know even less about Macros?? I don't know how to implement this macro.

You can install either Peter's macro from Message #4 or my macro from Message #6 as follows...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name, either CombineAndEliminate or RowsOfNumbersToSingleColumn depending on which macro you use, from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[dwrowe001]

And I only raised it because of how surprised I was at its speed.




I know you grasped it... I'm pretty sure on first reading... that explanation was more for current and future readers of this thread than for you, it's just your comment gave me the opening to explain it.




Yes, of course, as is always the case.

Hi Rick,
can you help me with this Macro... how do I use it?

Dave

 

[Rick Rothstein]

Hi Rick,
can you help me with this Macro... how do I use it?

Our messages crossed in the "ether"... see Message #12 .

 

[dwrowe001]

You can install either Peter's macro from Message #4 or my macro from Message #6 as follows...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name, either CombineAndEliminate or RowsOfNumbersToSingleColumn depending on which macro you use, from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Rick,
Sorry bout that, I posted my question to you before I saw your reply...
I'll give it a shot.

 

[dwrowe001]

Rick,
Would it be totally out of the question to do what I need with a formula?
I am just curious, can the macro update each time the number strings I mentioned in my original post get up dated? Can I update the number lists and then the macro will run?

 

[Peter_SSs]

Would it be totally out of the question to do what I need with a formula?

I think so.

I am just curious, can the macro update each time the number strings I mentioned in my original post get up dated? Can I update the number lists and then the macro will run?

If the numbers in those various ranges are updated manually, yes. If any of those 99 cells contain formulas, then it still could be automated but I would seek some more information before suggesting a particular method.

Assuming ..
A. Values are updated manually
B. Results are to go on an existing sheet called 'Result' (just edit the code in one place if a different name)
C. Any existing data in column A of the 'Result' sheet can be removed each time
D. You have formatted column A of the 'Result' sheet with the custom format of "00" like your data sheet.

... try this

On the sheet with the main data ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by changing or re-entering one of the numbers in one of the ranges in question. The code should run automatically whenever one or more of the values are changed.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm). You may need to enable macros, make this a Trusted Workbook etc depending on your settings in Excel.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim d As Object
  Dim c As Range
  
  Const myRange As String = "D12:R12,D14:R14,D16:R16,D18:R18,D20:R20,D32:M32,D33:K33,D34:I34"
  
  If Not Intersect(Target, Range(myRange)) Is Nothing Then
    Set d = CreateObject("Scripting.Dictionary")
    For Each c In Range(myRange)
        If Val(c.Text) <> 0 And Val(c.Text) <= 60 Then d(c.Text) = 1
    Next c
    With Sheets("Result")
      .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).ClearContents
      If d.Count > 0 Then .Range("A1").Resize(d.Count).Value = Application.Transpose(d.keys)
    End With
  End If
End Sub

 

[dwrowe001]

I think so.


If the numbers in those various ranges are updated manually, yes. If any of those 99 cells contain formulas, then it still could be automated but I would seek some more information before suggesting a particular method.

Assuming ..
A. Values are updated manually
B. Results are to go on an existing sheet called 'Result' (just edit the code in one place if a different name)
C. Any existing data in column A of the 'Result' sheet can be removed each time
D. You have formatted column A of the 'Result' sheet with the custom format of "00" like your data sheet.

... try this

On the sheet with the main data ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by changing or re-entering one of the numbers in one of the ranges in question. The code should run automatically whenever one or more of the values are changed.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm). You may need to enable macros, make this a Trusted Workbook etc depending on your settings in Excel.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim d As Object
  Dim c As Range
  
  Const myRange As String = "D12:R12,D14:R14,D16:R16,D18:R18,D20:R20,D32:M32,D33:K33,D34:I34"
  
  If Not Intersect(Target, Range(myRange)) Is Nothing Then
    Set d = CreateObject("Scripting.Dictionary")
    For Each c In Range(myRange)
        If Val(c.Text) <> 0 And Val(c.Text) <= 60 Then d(c.Text) = 1
    Next c
    With Sheets("Result")
      .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).ClearContents
      If d.Count > 0 Then .Range("A1").Resize(d.Count).Value = Application.Transpose(d.keys)
    End With
  End If
End Sub

Thank you both, Rick and Peter for your input and help... I have learned a bit about Macros and my questions have been answered.

 

[Peter_SSs]

Thank you both, Rick and Peter for your input and help... I have learned a bit about Macros and my questions have been answered.

Glad you have a satisfactory outcome. Thanks for letting us know.