CODEWITHSUNDEEP
vbaexcelexcel-formula
user9272092
user9272092

Reputation:

Create alphanumeric list based on varying cell value

So I have a set of data with around 120 parents each with between 8-16 child data entries. Each parent is indexed by number and I'm trying to get each child to have the parents number followed by an alphabetic character e.g.

A    B
58   Parent entry
58A  Child 1
58B  Child 2

It currently looks like (with child entries index cells being blank):

A   B
58  Parent entry
    Child entry 1
    Child entry 2

How would I go about automatically (I guess via VBA) filling in the appropriate index for each child entry based on their respective parent entry (both parent and child are in the same column). Note: Each parent has a different number of children entries.

Upvotes: 1

Views: 121

Answers (3)

CallumDA
CallumDA

Reputation: 12113

Doable without VBA. I split the process down across a few columns. Formulas below:

enter image description here

B2: =IF(ISERROR(SEARCH(":",A2)),"Child","Parent")
C2: =IF(B2="Parent",LEFT(A2,SEARCH(":",A2)-1),C1)
D2: =IF(B2="Child",C2&CHAR(63+COUNTIF($C$2:C2,C2)),C2)

Requested explanation of D2 formula above:

COUNTIF($C$2:C2,C2) counts how many times the value in C2 has been found in column C up to the current row (current being the row with the formula in)

So for example. D4 can count three 58s from C2:C4, similarly D8 can count seven 58s in C2:C8.

Consider D4 again, it counts three 58s so the formula is equivalent to this:

=IF(B4="Child",C4&CHAR(63+3),C4)

CHAR(65) returns A up to CHAR(90) returning Z. So in this case it follows that CHAR(66) returns B. Hence:

=IF(B4="Child",C4&"B",C4)

Upvotes: 3

DisplayName
DisplayName

Reputation: 13386

assuming your data starts at cell A1

Sub AddParentNumber()    
    With Range("A1").CurrentRegion.Resize(, 1)
        .AutoFilter Field:=1, Criteria1:="="
        With .SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "= concatenate(OFFSET(R1C,MATCH(1,R1C:R[-1]C,-1)-1,0), UNICHAR(64+row()-MATCH(1,R1C:R[-1]C,-1)))"
            .value = .value
        End With
        .Parent.AutoFilterMode = False
    End With
End Sub

Upvotes: 0

user9272092
user9272092

Reputation:

Building on @CallumDA answer, using the same format as him the formulas I used in the end were:

B2: =IF(NOT(ISBLANK(A7)),"Total",IF(ISERROR(SEARCH("",A6)),"Child","Parent"))

C2: =IF(B6="Total","",IF(B6="Parent",A6,C5))

D2: =IF(B6="Child",C6&CHAR(63+COUNTIF($C$6:C6,C6)),C6)

Upvotes: 0

Related Questions