Question to PAB

G

Grandmaster

Member
Hi PAB,

I would appreciate any idea,

How could I do the following in excel?

Ex.

I have 6 numbers in 6 columns:

1 4 7 8 11 14
2 3 6 9 12 13
.
.
.
.

I need the 6 numbers but in 14 columns (if no number then ",") :

1,,4,,78,,11,,14
,23,,6,,9,,1213,
.
.
.
.

Regards
 
G

Grandmaster

Member
Editing first message:

I have 6 numbers in 6 columns:

1 4 7 8 11 14
2 3 6 9 12 13
.
.
.
.

I need the 6 numbers but in 14 columns (if no number then ",") :

1,,4,,7,8,,11,,14
,2,3,,6,,9,,12,13,
.
.
.
.

This is the idea,

Regards
 
P

PAB

Member
Hi Grandmaster,

Grandmaster said:
I have 6 numbers in 6 columns :-

1 4 7 8 11 14
2 3 6 9 12 13

I need the 6 numbers but in 14 columns (if no number then ",") :-

1,,4,,7,8,,11,,14
,2,3,,6,,9,,12,13,
Click to expand...
If I Understand you Correctly, Either of these Two Formulas will do what you want.
Assuming your Numbers are in Cells "A1:N1", then Enter Either of these Two Formulas in Cell "P1" and Copy Down.

Formula 1 :-

=CONCATENATE(A1,",",B1,",",C1,",",D1,",",E1,",",F1,",",G1,",",H1,",",I1,",",J1,",",K1,",",L1,",",M1,",",N1)

Formula 2 :-

=A1&","&B1&","&C1&","&D1&","&E1&","&F1&","&G1&","&H1&","&I1&","&J1&","&K1&","&L1&","&M1&","&N1

Both Formulas give Exactly the Same Result.

I Hope this Helps.
All the Best.
PAB
:wavey:
 
Last edited:
bloubul

bloubul

Member
Hi PAB

I hope that everything is OK with you.

PAB I need some help from you as usual. I'm following the treads of Side by Side strategy and ELIMINATOR, but I can't for one or more reasons get it to work on a 6/49 not even close. Can you perhaps help or having an idea that one can use.

Thanks in advance

BlouBul :cool:
 
P

PAB

Member
Hi BlouBul,

Everything is O.K. with me Thanks, how about yourself?.
I have NOT been Following the Side by Side Strategy OR ELIMINATOR. I will However, Read & Catch Up on what they are all about and get Back to you. I am Quite Busy at Work at the Moment so it Might take me a Few Days to Reply.

All the Best.
PAB
:wavey:
 
bloubul

bloubul

Member
bloubul said:
Hi PAB

I'm fine, just can't get the numbers straight. Thanks for your help.

BlouBul :cool:
Click to expand...



Hi PAB

Just wanna know how you are doing with the Side by Side and ELIMINATOR theory.


BlouBul :cool:
 
P

PAB

Member
Hi BlouBul,

I will Hopefully have Time to have a Good Look at them over the Next Couple of Days.

All the Best.
PAB
:wavey:
 
P

PAB

Member
Hi BlouBul,

I have been away from Home for about 4 Weeks. I have just enough time to check my emails and Browse this Forum. I am back at Home the Middle of Next Week and will make an Effort to Access the ELIMINATOR and ISOLATOR Threads.

All the Best.
PAB
:wavey:
 
bloubul

bloubul

Member
Hi PAB

How are you keeping? What is the latest news on the ELIMINATOR and ISOLATOR in Excel.

Please help me again. I have the following selection (below).
As you can see there are duplicates. What formula, macro can I use
to remove all 2nd and more duplicates, but keep the first number.
Please help.

11 25 35 38 39
06 10 13 26 35 43
27 34 36 43 47
04 07 27 38 40
07 10 12 14 34 50
12 16 17 23 38 47
14 21 22 30 32 35
09 14 16 18 38 43
12 26 32 40
06 08 24 26 30 47


Thanks

BlouBul :cool:
 
P

PAB

Member
Hi BlouBul,

Do you just want a List of the Unique Numbers One Under the Other?, OR do you want the Rows with the Combinations Left but the Duplicates Blanked Out?.

All the Best.
PAB
:wavey:
 
P

PAB

Member
Hi BlouBul,

Try this Macro Out for Size.
Run the Macro.
In the Input Box, just Type in the Range of Cells that you Require ( B2:H10 for Example ).
The Macro Leaves the Rows AND Cells Intact and just BLANKS the Duplicates.

Code: 
Sub Blank_Duplicates()
Dim rng As Range
Dim r As Range
Application.ScreenUpdating = False
On Error GoTo Last
Set rng = Application.InputBox("Select Range", Type:=8)
On Error GoTo 0
With CreateObject("Scripting.Dictionary")
    For Each r In rng
         If Not .exists(r.Value) Then
             .Add r.Value, Nothing
         Else
             r.ClearContents
         End If
    Next
End With
Last:
Application.ScreenUpdating = True
End Sub
Hope this Helps.
All the Best.
PAB
:wavey:
 
bloubul

bloubul

Member
Hi PAB

Happy Birthday to you.

PAB I came across this macro of yours for selecting 7/27 numbers.
Will you please modify it for me to a 6/36 numbers.

Option Explicit
Dim A As Integer, B As Integer, C As Integer, D As Integer
Dim E As Integer, F As Integer, G As Integer, N As Long
Sub List_Combinations_27()
Range("A1").Select
Application.ScreenUpdating = False
N = 0
For A = 1 To 21
For B = A + 1 To 22
For C = B + 1 To 23
For D = C + 1 To 24
For E = D + 1 To 25
For F = E + 1 To 26
For G = F + 1 To 27
N = N + 1
If N = 65001 Then
N = 1
ActiveCell.Offset(-65000, 8).Select
End If
ActiveCell.Offset(0, 0).Value = A
ActiveCell.Offset(0, 1).Value = B
ActiveCell.Offset(0, 2).Value = C
ActiveCell.Offset(0, 3).Value = D
ActiveCell.Offset(0, 4).Value = E
ActiveCell.Offset(0, 5).Value = F
ActiveCell.Offset(0, 6).Value = G
ActiveCell.Offset(1, 0).Select
Next G
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub

Thanks

BlouBul :cool:
 
P

PAB

Member
Thanks BlouBul,

This Macro should do what you want.

Code: 
Option Explicit
Sub List_Combinations_36()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim N As Long

Range("A1").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
N = 0
For A = 1 To 31
    For B = A + 1 To 32
        For C = B + 1 To 33
            For D = C + 1 To 34
                For E = D + 1 To 35
                    For F = E + 1 To 36
                        N = N + 1
                        If N = 65001 Then
                            N = 1
                            ActiveCell.Offset(-65000, 7).Select
                        End If
                        ActiveCell.Offset(0, 0).Value = A
                        ActiveCell.Offset(0, 1).Value = B
                        ActiveCell.Offset(0, 2).Value = C
                        ActiveCell.Offset(0, 3).Value = D
                        ActiveCell.Offset(0, 4).Value = E
                        ActiveCell.Offset(0, 5).Value = F
                        ActiveCell.Offset(1, 0).Select
                    Next F
                Next E
            Next D
        Next C
    Next B
Next A
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Hope this is what you want.
All the Best.
PAB
:wavey:
 
bloubul

bloubul

Member
Hi PAB

Yes it is working beautiful, thank you so much. Tel me do you have these macro's and excel formula's in PDF form.

PAB help again please, like the macro you did for me on the "Removal" of duplicates, what is the chance for a macro that delete all sums that is in a range <xx value and >yy value.

Thanks

BlouBul :cool:
 
You must log in or register to reply here.

Sidebar

Top