excel 2007 formula to combine like pairs

[LuGyver]

Thank you for your help!
Using excel 2007.
need to combine like pairs and keep the results in one row.

A1:AK1 holds the pairs
-The pairs are generated by a formula and are not sorted. The format is general and there are no duplicates in this row.

A2:indefinate2 (as far right as it needs to go) needs to hold the formula of which combines the pairs.
It is okay if they are, or, are not sorted in the final outcome as long as the combined results are all in one row.

Also, I could have made mistakes when I penciled the information below. Coming close to it is okay and i Hope you see the jest of it.

Example of Pairs & Combined Pairs:
- I Sorted Pairs to make it easier to see
[TABLE="class: MsoNormalTable, width: 723"]
<tbody>[TR]
[TD] 01[/TD]
[TD="width: 25"] 11[/TD]
[TD="width: 25"] 15[/TD]
[TD="width: 25"] 21[/TD]
[TD="width: 25"] 30[/TD]
[TD="width: 25"] 31[/TD]
[TD="width: 25"] 33[/TD]
[TD="width: 25"] 34[/TD]
[TD="width: 25"] 40[/TD]
[TD="width: 25"] 41[/TD]
[TD="width: 25"] 43[/TD]
[TD="width: 25"] 44[/TD]
[TD="width: 25"] 46[/TD]
[TD="width: 25"] 48[/TD]
[TD="width: 25"] 53[/TD]
[TD="width: 25"] 54[/TD]
[TD="width: 23"] 55[/TD]
[TD="width: 23"] 57[/TD]
[TD="width: 23"] 59[/TD]
[TD="width: 23"] 61[/TD]
[TD="width: 23"] 64[/TD]
[TD="width: 23"] 66[/TD]
[TD="width: 23"] 68[/TD]
[TD="width: 23"] 83[/TD]
[TD="width: 23"] 86[/TD]
[TD="width: 23"] 93[/TD]
[/TR]
</tbody>[/TABLE]

Notice there are pairs 01 30 40 all contain "0". So, the formula need to achieve 01 and 03 = 013, 01 and 04 = 014.

Penciled the rest of the like pairs out so you can see what the combined results should look like:
11 15 21 31 41 61 = 115 112 113 114 116
15 55 = 155
21 31 41 61 = 213 214 216
30 10 40 = 301 104 304
31 41 61 = 314 316
33 43 53 83 93 = 334 353 383 393
34 44 54 64 = 344 354 364
41 61 = 416
43 83 = 483
44 46 4854 = 446 445 448
46 64 = 466
48 64 = 486
53 83 93 = 538 539
54 64 = 564
66 68 86 = 668
68 83 = 683
83 31 43 53 93 = 831 834 835
83 93 = 893


I have tried different variations of sumproduct but always have to involve more than one row.
Here's one example
=IF(SUMPRODUCT(--(LEFT($A$1:$A$1,1)=LEFT(B$1))),LEFT($A$1,1)&B$1,IF(SUMPRODUCT(--(LEFT($A$1:$A$1,1)=RIGHT(B$1))),LEFT($A$1,1)&B$1,IF(SUMPRODUCT(--(RIGHT($A$1:$A$1,1)=LEFT(B$1))),RIGHT($A$1,1)&B$1,IF(SUMPRODUCT(--(RIGHT($A$1:$A$1,1)=RIGHT(B$1))),RIGHT($A$1,1)&B$1,""))))

 

[Eric W]

Interesting question, but I don't see what you want as a result. You've shown how certain pairs, and how they combine, but not which of the combinations you want. You've shown a sample row 1 with the inputs, please show a sample row 2 with the results you want (and row 3, 4, etc. if needed).

Also, the examples for combining pairs seem a bit inconsistent. For example, you have 41 and 61 concatenating to get 4161, then remove the last common digit, giving 416. Then you show 43 and 83 concatenating to get 4383, then remove the FIRST common digit, giving 483. I don't follow the logic.

 

[Peter_SSs]

IFor example, you have 41 and 61 concatenating to get 4161, then remove the last common digit, giving 416. Then you show 43 and 83 concatenating to get 4383, then remove the FIRST common digit, giving 483. I don't follow the logic.

Further to Eric's point,

1. You show 46 64 = 466. Here, you have removed one of the common digits (4) but not the other common digit (6)

2. You said "Penciled the rest of the like pairs out ..". Why are there no pairs beginning with 40 or 55 or 57 etc?

3. You listed 30 10 40 but there is no "10" in the data and I can't see what it could have been if it was a typo.

4. You have listed 83 31 43 53 93 = 831 834 835. This has two things I don't understand
- The left hand pairs are not in ascending order like all the rest (except for the one mentioned in point 3 that may have been a typo)
- There are 5 pairs on the left but only 3 results on the right when I would expect 4. Why isn't 839 a result?

Remembering that we are not familiar with your data or requirements, you have to set it out very carefully & clearly with both words and accurate results if we are to have a chance of understanding well enough to make useful suggestions for you.

 

[LuGyver]

Thank you Eric W and Peter_SSs.
Sorry guys, everything is beautiful in my head! I find it difficult to make clear what I am struggling with. BTW this is a lottery thing.
Using a pencil and paper, I didn't realize I chose mindlessly during presentation.

Literally though, in an unsorted row, which is what I have, I take the first pair (from left to right) and look for like digits in the rest of the pairs.
The first pair is kept. The following pairs like-digit is removed, and the remaining digit is added to the first pair.
Example:
Unsorted pairs In Row 1:
[TABLE="width: 723"]
<tbody>[TR]
[TD][TABLE="width: 634"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 761"]
<tbody>[TR]
[TD]64[/TD]
[TD]41[/TD]
[TD]21[/TD]
[TD]43[/TD]
[TD]30[/TD]
[TD]15[/TD]
[TD]31[/TD]
[TD]40[/TD]
[TD]53[/TD]
[TD]11[/TD]
[TD]44[/TD]
[TD]46[/TD]
[TD]68[/TD]
[TD]48[/TD]
[TD]57[/TD]
[TD]59[/TD]
[TD]83[/TD]
[TD]86[/TD]
[TD]93[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]66[/TD]
[TD]54[/TD]
[TD]55[/TD]
[TD]61[/TD]
[TD]01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

So, from left to right, 64 is my first pair. I look through the rest of the pairs (left to right after 64) to find any other pair that holds a 6, or a 4.
I find 41, 43, 40, 44, 46, 68, 48, 86, 34, 66, 54, 61

Of the found like pairs, we have the first pair 41.
I remove the like digit 4 and keep the other digit 1. So, keeping the 64 pair, take 41... drop the like digit 4 from the it and apply the remaining digit 1 to 64 to get 641.
Next is 43. drop the like digit 4 and apply 3 to 64 to get 643
Next is 40. Drop the 4 and keep 0 and apply it to 64 to get 640.
now we find a 44 pair. Just keep one 4 and apply it to 64 to get 644
now we find a 46 pair. Just keep the larger digit 6 and apply it to 64 to get 646
continue on until the first pair 64 has exhausted all the like pairs found.

Now Row 1 and Row 2 look like this
[TABLE="width: 761"]
<tbody>[TR]
[TD]64[/TD]
[TD]41[/TD]
[TD]21[/TD]
[TD]43[/TD]
[TD]30[/TD]
[TD]15[/TD]
[TD]31[/TD]
[TD]40[/TD]
[TD]53[/TD]
[TD]11[/TD]
[TD]44[/TD]
[TD]46[/TD]
[TD]68[/TD]
[TD]48[/TD]
[TD]57[/TD]
[TD]59[/TD]
[TD]83[/TD]
[TD]86[/TD]
[TD]93[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]66[/TD]
[TD]54[/TD]
[TD]55[/TD]
[TD]61[/TD]
[TD]01[/TD]
[/TR]
</tbody>[/TABLE]
641 643 640 644 646 648 648 648 643 646 645 641

We've exhausted the like pairs for 64, so time to move to the right one pair in Row 1 (after 64) which is pair 41.
Looking for like pairs again from left to right (starting after the pair 41) is 21, 43, 15, 31, 40, 11, 44, 46, 48, 34, 54, 61, 01
41 and 21.. drop like digit 1 & keep 2 to get 412
41 and 43.. drop like digit 4 & keep 3 to get 413
and so on.
We've exhausted the like pairs for 41, so it's time to move to the right one pair in Row 1 (after 41)
which is pair 21. After we exhaust like pairs for 21; 15 31 11 61 We move to the right one pair in Row 1 (after 21) which is pair 43.
so 43; 30 31 40 53 44 46 48 83 93 33 34 54

Altogether Row 1 and Row 2 (colored to see the change).

[TABLE="width: 761"]
<tbody>[TR]
[TD]64[/TD]
[TD]41[/TD]
[TD]21[/TD]
[TD]43[/TD]
[TD]30[/TD]
[TD]15[/TD]
[TD]31[/TD]
[TD]40[/TD]
[TD]53[/TD]
[TD]11[/TD]
[TD]44[/TD]
[TD]46[/TD]
[TD]68[/TD]
[TD]48[/TD]
[TD]57[/TD]
[TD]59[/TD]
[TD]83[/TD]
[TD]86[/TD]
[TD]93[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]66[/TD]
[TD]54[/TD]
[TD]55[/TD]
[TD]61[/TD]
[TD]01[/TD]
[/TR]
</tbody>[/TABLE]
641 643 640 644 646 648 648 648 643 646 645 641 412 413 415 413 410 411 414 416 418 413 415 416 410 215 213 211 216 430 431 430 etc.

Hope this makes better sense... Should have asked if this is even possible. Thanks for putting up with me

 

[Peter_SSs]

I don't see a feasible formula solution for this so I have suggested a macro.
Its results seem to be matching what you have described and show. There are 122 results in total.

Sub CombinePairs()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, x As Long, y As Long, k As Long, UBa As Long
  Dim s As String

  a = Range("A1", Range("A1").End(xlToRight)).Value
  UBa = UBound(a, 2)
  ReDim b(1 To 1, 1 To 1)
  For i = 1 To UBa - 1
    s = a(1, i)
    For j = i + 1 To UBa
      x = Left(a(1, j), 1)
      y = Right(a(1, j), 1)
      If InStr(1, s, x) > 0 Or InStr(1, s, y) > 0 Then
        If InStr(1, s, x) = 0 Then s = s & x
        If InStr(1, s, y) = 0 Then s = s & y
        If Len(s) = 2 Then s = s & IIf(x > y, x, y)
        k = k + 1
        ReDim Preserve b(1 To 1, 1 To k)
        b(1, k) = s
        s = a(1, i)
      End If
    Next j
  Next i
  If k > 0 Then
    With Range("A2").Resize(, k)
      .NumberFormat = "@"
      .Value = b
    End With
  End If
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	64	41	21	43	30	15	31	40	53	11	44	46	68	48	57	59	83	86	93	33	34	66	54	55	61	01
2	641	643	640	644	646	648	648	648	643	646	645	641	412	413	415	413	410	411	414	416	418	413	415	416	410	215	213	211	216	210	430	431	430
Pairs

 

[LuGyver]

That does work for one row indeed.
question:
I need it to look at more than one row. is there a way to tell the macro to look at multiple rows at once?

 

[Rick Rothstein]

That does work for one row indeed.
question:
I need it to look at more than one row. is there a way to tell the macro to look at multiple rows at once?

What do you mean by "multiple rows at once"? Do you mean the calculations should take place as if Row 2 values were listed on Row 1 after the values already on Row 1? And if there were values on Row 3, the calculations should take place as if Row 3 value were listed after Row 2 value on Row 1? And so on for all of your multiple rows? If so, about how many rows do you have and what is the maximum number of columns that might get filled with data? If not, and you mean you have multiple individual rows that have to be processed individually, then where is the output supposed to go to (Peter's output goes directly under the values themselves which, of course, can't happen if there is data already there)?

 

[LuGyver]

What do you mean by "multiple rows at once"? Do you mean the calculations should take place as if Row 2 values were listed on Row 1 after the values already on Row 1? And if there were values on Row 3, the calculations should take place as if Row 3 value were listed after Row 2 value on Row 1? And so on for all of your multiple rows? If so, about how many rows do you have and what is the maximum number of columns that might get filled with data? If not, and you mean you have multiple individual rows that have to be processed individually, then where is the output supposed to go to (Peter's output goes directly under the values themselves which, of course, can't happen if there is data already there)?

opps my bad. I only used A1 and A2 for examples and did not count on macro coming into play. again I didn't convey well.
First post is looking for a formula. so I would have simply moved the formula where I want it, then filled up/down as needed.

okay let's do this since I am not familiar with macro/vb.
note: the data is created by calculation and changes each time I create a new row by filling down. The prior rows hold/keep their results as a history to the right of each rows data set.

Actual Data A1:AT20 Results BA1:BA20
A1:AT1 holds the first set of data ---- BA1:B?1 reaps only results from A1:AT1 and keeps it there
A2:AT2 holds the next set of data ---- BA2:B?2 reaps only results from A2:AT2 and keeps it there
A3:AT3 and so on
Fill down to create data A4:AT4 which holds the next set of data ---- BA4:BT4 is now blank until macro create results from A4:AT2 and keeps it there

When a new row is created by filling down, the macro would be creating results from the new row, which is created, and not the past rows.
It sounds like the macro would have to create the fill down, and the results. But as I said, I know nothing about macro/vb

Hope this makes better sense

 

[Peter_SSs]

A1:AT1 holds the first set of data ---- BA1:B?1 reaps only results from A1:AT1 and keeps it there
A2:AT2 holds the next set of data ---- BA2:B?2 reaps only results from A2:AT2 and keeps it there
A3:AT3 and so on
Fill down to create data A4:AT4 which holds the next set of data ---- BA4:BT4 is now blank until macro create results from A4:AT2 and keeps it there

When a new row is created by filling down, the macro would be creating results from the new row, which is created, and not the past rows.
It sounds like the macro would have to create the fill down, and the results.

No, you can still fill down the formulas in A:AT, although a macro could do that if you want. The new macro below will automatically fill in the results for the new row. If you fill down for say 3 rows then the macro will automatically fill in the results for those 3 rows.

To implement the new macro (testing in a copy of your workbook), follow these steps..
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 dragging the A:AT formulas down.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, x As Long, y As Long, k As Long, UBa As Long, r As Long, lrL As Long, lrR As Long
  Dim s As String
  
  If Not Intersect(Target, Columns("A:AT")) Is Nothing Then
    lrL = Columns("A").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    If Not IsEmpty(Range("BA1").Value) Then lrR = Columns("BA").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    If lrL > lrR Then
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      For r = lrR + 1 To lrL
        a = Range("A" & r & ":AT" & r).Value
        UBa = UBound(a, 2)
        ReDim b(1 To 1, 1 To 1)
        For i = 1 To UBa - 1
          s = a(1, i)
          For j = i + 1 To UBa
            x = Left(a(1, j), 1)
            y = Right(a(1, j), 1)
            If InStr(1, s, x) > 0 Or InStr(1, s, y) > 0 Then
              If InStr(1, s, x) = 0 Then s = s & x
              If InStr(1, s, y) = 0 Then s = s & y
              If Len(s) = 2 Then s = s & IIf(x > y, x, y)
              k = k + 1
              ReDim Preserve b(1 To 1, 1 To k)
              b(1, k) = s
              s = a(1, i)
            End If
          Next j
        Next i
        If k > 0 Then
          With Range("BA" & r).Resize(, k)
            .NumberFormat = "@"
            .Value = b
          End With
          k = 0
        End If
      Next r
      Application.ScreenUpdating = True
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[LuGyver]

Thank you. worked once really good. was impressive but after that doesn't do anything more. such as return results for new row.

In my inexperience, sometimes I need to remove the row I filled down and redo the calculations..., sometimes the data (might have) a blank, but for most part the history is solid.

Thinking maybe code needs a button to perform the fill down when I''m ready, then combine new pairs. if history is blank then, fill history also... maybe?

also I had a lot of information in my sheet which involves a lot of history and needed cells to calculate it. my examples were way to simple, so I wonder if I inadvertently smudged the outcome.

Just so I am not leaving out something important, going to say my vb test sheet has data in H11563:BA11837 and results start in BK11563:To-However-far-right as needed.

Which brings me to ask, how does one go about moving the code to another sheet such as the original sheet with all the information? I think I need to simply change the reference in the code if I did move it, but that's just me.

Thank you Peter