Format column with text and number

[mrtobsy]

Hi,

Not sure how to formulate this, but i'd like to know how to add numbers (and have it automatically updated) to a column filled with text.

Example:

I have a list like that:

john (1)
ben (2)
pete (3)
henry (4)

and i want to add edouard (3):

john (1)
ben (2)
edouard (3)
pete (3)
henry (4)

How do i do so that the numbers get updated automatically (ie pete (3) -> (4) and henry (5)-> (6))

Secondly, how can i number the cells in the columns (ie (1), (2)) automatically (if i have 150 names to list and numbered like the example above).

For my example, i assume that text+number are in the same cell. Maybe i could have one cell for each value and merge them(just thought about it), but don't if it would work and if practical or not?

Appreciate any helps,

Thanks

 

[erik.van.geit]

Hi, mrtobsy
Welcome to the Board !!!!!

to keep things simple I wouldn't put both items in the same cell
do you really want to use at all cost this counter-excel-intuitive solution
we can cooperate when you can clarify this: "everything is do-able"

how are you going to "add" edouard ?
seems more like you are going to "insert" the name between the others ...
needs also clarification to my sense

take a look at the function ROW() in the helpfiles

remark: "merge" is not the same as "concatenate"

example
  A     B       C           
1 names numbers concatenate 
2 john  1       john (1)    
3 ben   2       ben (2)     
4 pete  3       pete (3)    
5 henry 4       henry (4)   

test
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
B2:B5 =ROW()-ROW(B$1)
C2:C5 =A2&" ("&B2&")"

[Table-It] version 06 by Erik Van Geit

insert row and copy formulas
  A     B       C           
1 names numbers concatenate 
2 john  1       john (1)    
3 ben   2       ben (2)     
4       3        (3)        
5 pete  4       pete (4)    
6 henry 5       henry (5)   

test
[Table-It] version 06 by Erik Van Geit

fill in edouard
  A       B       C           
1 names   numbers concatenate 
2 john    1       john (1)    
3 ben     2       ben (2)     
4 edouard 3       edouard (3) 
5 pete    4       pete (4)    
6 henry   5       henry (5)   

test
[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[erik.van.geit]

couldn't resist to try out some code
assuming the items are in column one starting at row 2

Sub test()
'Erik Van Geit
'061016

Dim rng As Range        'range with data
Dim arr As Variant      'array
Dim LR As Long          'Last Row
Dim i As Long
Dim ch As Integer

LR = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & LR)

arr = rng

    For i = 1 To rng.Rows.Count
    ch = 0
    ch = InStr(1, arr(i, 1), " (")
    If ch Then arr(i, 1) = Left(arr(i, 1), ch - 1)
    arr(i, 1) = arr(i, 1) & " (" & i & ")"
    Next i

rng = arr

End Sub

EDIT: handling empty cells (leaving empty)

Option Explicit


Sub test()
'Erik Van Geit
'061016

Dim rng As Range        'range with data
Dim arr As Variant      'array
Dim LR As Long          'Last Row
Dim i As Long
Dim ch As Integer

LR = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & LR)

arr = rng

    For i = 1 To rng.Rows.Count
        If arr(i, 1) <> vbNullString Then
        ch = 0
        ch = InStr(1, arr(i, 1), " (")
        If ch Then arr(i, 1) = Left(arr(i, 1), ch - 1)
        arr(i, 1) = arr(i, 1) & " (" & i & ")"
        End If
    Next i

rng = arr

End Sub

 

[mrtobsy]

Thank you very much erik.van.geit for your help.
You're right, what i want to do is insert edouard between the others and have the "number" updated automatically. I guess concatenate is closer to what i want to do, but that'd mean that i'd have 3 columns (A w/text, B w/ number and C w/ formula) which wouldn't help me much.

Let me try something else:

If i only have two columns

A B
1 names numbers (order #)
2 john 1
3 ben 2
4 pete 3
5 henry 4



and i want column A to be associated with column B (ie john->1, ben->2, etc...). Now, i insert a text value in between A3 and A4 "Edouard", column B is (auto)updated/or i'd input the order #"3". Column B is updated to show that edouard is order #3 on the list, pete and henry would be bumped down to order #4 and #5 respectively. Is it possible to have such a system w/o having to input the numbers all the time?

Summary:

-Linking column A to B (ie john is linked to (nth))
-Inserting/Deleting a row would automatically update the other entries to show the change.

Don't know if it will yield the same result as my first post. If it has to be done on a daily basis & long term, i might have to find another solution than excel probably.

Thank for the help,

mrtobsy

 

[erik.van.geit]

please provide feedback on the code I posted

it was a bit of a curve of 180° I made from reply 1 to reply 2
when trying out without code, I found out that inserting a row + copying down formulas would need code also, therefore my curiosity to find an "all-in-one-solution" led me to the code ...

but you didn't tell anything about it

so, to get further help:
put some names in column A
run the code
insert some names
run the code
provide feedback

(works nicely for me)