Generating a code for a dataset

[wannabe12]

Hi there,

I have this dataset column A, B and C. In column B are Batch Number (e.g. 26848), Fund number (e.g. 400), and activity Codes, (e.g. 2ACQCOLD01).

The idea is to create a code when column B has an activity code. For example 26848 is the Batch number, then 400 is the fund number, now the next value is the activity code 2ACQCOLD01, at this point the code is 26848,400,2ACQCOLD01. However, things become tricky as we move down. The next item is Batch 26849, and fund 81 and at 2IRSPOG a code is displayed (26849,81,2IRSPOG), but there is another fund and activity code and the code should be - 26849,85,2IRSPOG.

I have done this manually but is there a feature or code that can automate this?

Notes - the Batch numbers could be anything, the Fund numbers are known, the activity numbers always start with either a 1 or 2 or 3 or 4 or 5.

Any futher questions please do not hesitate to ask.

Thanks.

Amish


[TABLE="width: 328"]
<TBODY>[TR]
[TD][/TD]
[TD]26848
[/TD]
[TD="align: right"]200
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]400
[/TD]
[TD="align: right"]200
[/TD]
[/TR]
[TR]
[TD]26848,400,2ACQCOLD01
[/TD]
[TD]2ACQCOLD01
[/TD]
[TD="align: right"]200
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26849
[/TD]
[TD="align: right"]857
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]81
[/TD]
[TD="align: right"]75
[/TD]
[/TR]
[TR]
[TD]26849,81,2IRSPOG
[/TD]
[TD]2IRSPOG
[/TD]
[TD="align: right"]75
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]85
[/TD]
[TD="align: right"]75
[/TD]
[/TR]
[TR]
[TD]26849,85,2IRSPOG
[/TD]
[TD]2IRSPOG
[/TD]
[TD="align: right"]75
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]355
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2RETWARM3
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]400
[/TD]
[TD="align: right"]607
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2IRIMPACT
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2IRSAD
[/TD]
[TD="align: right"]240
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2IRSPOG
[/TD]
[TD="align: right"]167
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2RETP22
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26850
[/TD]
[TD="align: right"]540
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]356
[/TD]
[TD="align: right"]300
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1MGTRUST
[/TD]
[TD="align: right"]300
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[HWL]

Let me understand... You presently have 3 columns of data. Col A is Batch numbers, Col B is Fund number, and Col C is activity code????
I guess I don't quite understand how the data is laid out and what/where the result should be...and the basis for the result.

 

[wannabe12]

Hi there, thanks for respondsing.

Basically in column B there can be three types of data, Batch Number, Fund Number and Activity Code (not in three separate columns). In the example above in column B there is 26848 (Batch Number), then 400 (fund number), and then 2ACQCOLD01 (Activity Code). Wherever there is an activity code in column B a code like "26848,400,2ACQCOLD01" in the above, should be produced on the same line in column A.

Hope this helps
Kind Regards
Amish

 

[HWL]

Hi there, thanks for respondsing.

Basically in column B there can be three types of data, Batch Number, Fund Number and Activity Code (not in three separate columns). In the example above in column B there is 26848 (Batch Number), then 400 (fund number), and then 2ACQCOLD01 (Activity Code). Wherever there is an activity code in column B a code like "26848,400,2ACQCOLD01" in the above, should be produced on the same line in column A.

Hope this helps
Kind Regards
Amish

Hmm, when I help people design lists like this, the first thing I ask them about is, "What is going to be the 'unique identifier'?" It appears that in your case it is the Batch Number. It appears you want to associate individual activity codes and fund numbers with a specific Bathc Number.

In your first example, please let me know if this result would be correct for these Activity Codes:

* 26849,355,2RETWARM3
* 26849,400,2IRIMPACT
* 26849,400,2IRSAD
* 26849,400,2IRSPOG
* 26849,400,2RETP22
* 26850,356,1MGTRUST

Once I'm sure I understand what you are trying to do, I'll move forward (I'm not coding for Obamacare here )

 

[wannabe12]

Hi there,

Thats exactly right!

Thanks
Regards

Amish

 

[HWL]

one last question and then I think I can offer a solution....
Will the fund numbers be limited to 3 digits? If not, is there a maximum length?

 

[HWL]

Okay, all done. Check out Batch Combiner | RoderickE

The code is open for assessment and no other macros exist than the following:

Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
'-------------------------------rodericke.com-------------------
Sub combiner()
Dim scol As Integer
Dim dcol As Integer
Dim Rng As String
Dim resstr As String
Dim prevbnum As String
Dim prevfnum As Integer
scol = 2 'change source column as needed to column numer not letter
dcol = 1 'change destination column as needed to column numer not letter
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Rng = ConvertToLetter(dcol) & "1:" & ConvertToLetter(dcol) & lastrow 'create range for countif
For x = 2 To lastrow 'change 2 to data row (row after headers)
'batch number
If IsNumeric(Cells(x, scol)) And Cells(x, scol) > 999 Then
resstr = Cells(x, scol) & "," & Cells(x + 1, scol) & "," & Cells(x + 2, scol)
If Application.CountIf(Range(Rng), resstr) = 0 Then 'if non exist
Cells(x, dcol) = Cells(x, scol) & "," & Cells(x + 1, scol) & "," & Cells(x + 2, scol)
prevbnum = Cells(x, scol)
prevfnum = Cells(x + 1, scol)
End If
End If
'fund number
If IsNumeric(Cells(x, scol)) And Cells(x, scol) < 1000 Then
prevfnum = Cells(x, scol)
End If
If IsNumeric(Cells(x, scol)) And Cells(x, scol) < 1000 And InStr(Cells(x - 1, dcol), ",") = 0 Then
resstr = prevbnum & "," & Cells(x, scol) & "," & Cells(x + 1, scol)
If Application.CountIf(Range(Rng), resstr) = 0 Then 'if non exist
Cells(x, dcol) = prevbnum & "," & Cells(x, scol) & "," & Cells(x + 1, scol)
prevfnum = Cells(x, scol)
End If
End If
'activity code
If Application.Trim(Cells(x + 1, scol)) <> "" Then
resstr = prevbnum & "," & prevfnum & "," & Cells(x, scol)
If Application.CountIf(Range(Rng), resstr) = 0 Then 'if non exist
If IsNumeric(Cells(x, scol)) = False Then 'And IsNumeric(Cells(x + 1, scol)) = False Then
Cells(x, dcol) = prevbnum & "," & prevfnum & "," & Cells(x, scol)
End If
End If
End If
Next x
End Sub

 

[HWL]

the above code and workbook works but I want to tweak it a bit... will upload new version soon.

 

[HWL]

Okay all fixed up.
Added:
1. Clear previous results each RUN
2. Show message of count of combinations
3. Handling of trailing Activity Codes

See link: Batch Combiner | RoderickE

NEW CODE:

Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
'-----------------------rodericke.com--------------
Sub combiner()
Dim scol As Integer
Dim dcol As Integer
Dim Rng As String
Dim resstr As String
Dim prevbnum As String
Dim prevfnum As Integer
Dim cnt As Integer
scol = 2 'change source column as needed to column numer not letter
dcol = 1 'change destination column as needed to column numer not letter
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Rng = ConvertToLetter(dcol) & "1:" & ConvertToLetter(dcol) & lastrow 'create range for countif
'clear previous run
Range(Rng).ClearContents
For x = 2 To lastrow 'change 2 to data row (row after headers)
'batch number
If IsNumeric(Cells(x, scol)) And Cells(x, scol) > 999 Then
resstr = Cells(x, scol) & "," & Cells(x + 1, scol) & "," & Cells(x + 2, scol)
If Left(resstr, 1) <> "," Then
If Application.CountIf(Range(Rng), resstr) = 0 Then 'if non exist
Cells(x, dcol) = Cells(x, scol) & "," & Cells(x + 1, scol) & "," & Cells(x + 2, scol)
cnt = cnt + 1
prevbnum = Cells(x, scol)
prevfnum = Cells(x + 1, scol)
End If
End If
End If
'fund number
If IsNumeric(Cells(x, scol)) And Cells(x, scol) < 1000 Then
prevfnum = Cells(x, scol)
End If
If IsNumeric(Cells(x, scol)) And Cells(x, scol) < 1000 And InStr(Cells(x - 1, dcol), ",") = 0 Then
resstr = prevbnum & "," & Cells(x, scol) & "," & Cells(x + 1, scol)
If Left(resstr, 1) <> "," Then
If Application.CountIf(Range(Rng), resstr) = 0 Then 'if non exist
Cells(x, dcol) = prevbnum & "," & Cells(x, scol) & "," & Cells(x + 1, scol)
cnt = cnt + 1
prevfnum = Cells(x, scol)
End If
End If
End If
'activity code
resstr = prevbnum & "," & prevfnum & "," & Cells(x, scol)
If Left(resstr, 1) <> "," Then
If Application.CountIf(Range(Rng), resstr) = 0 Then 'if non exist
If IsNumeric(Cells(x, scol)) = False Then 'And IsNumeric(Cells(x + 1, scol)) = False Then
Cells(x, dcol) = prevbnum & "," & prevfnum & "," & Cells(x, scol)
cnt = cnt + 1
End If
End If
End If
Next x
MsgBox cnt & " combinations created.", vbInformation, "COMPLETE"
End Sub

 

[wannabe12]

Hi there,

It works....am absolutely delighted!!

Thanks

Amish