0

I have a data encoding project.

IMAGE

I will input my data in column A (equipment type), and the formulated cell would be in column B. Column B contains a string that starts with the value in column A, and appends an equipment-specific counter to it.

The different equipment types in column A can be in any order. The counter needs to increment within equipment type.

Whenever I input new data in a row in column A, the column B value would be automatically created, incrementing the counter by 1 for that equipment type.

In the example, the list starts with two type A's, whose counters are 001 and 002. Four rows later, there are more type A's, and their counters increment to 003 and 004.

In between, in rows 3, 4, and 5, there are types B, D, and C. Each of those has a counter starting at 001. Farther down, there are second types D and C, and the counter on each is incremented by 1. Then a second and third type B, and those counters are incremented to 002 and 003.

How can I generate the values in column B?

Improve this question
0

3 Answers 3

Reset to default
1

Solution Image

The formula in B2:

=IF(ISBLANK(A2),"",A2&"-"&TEXT(COUNTIF($A$2:A2,A2),"000"))

You can prepopulate the column B formula to an arbitrary large range and it will display a blank cell until you enter a value in column A.

It builds the displayed value by appending to the column A value, the hyphen and then the counter. The TEXT function with "000" format provides the leading zeros as needed for a three-digit counter.

COUNTIF counts the values in column A from the beginning through the current row that match the current column A value.

Improve this answer
1
  • Thanks! It works just like expected.
    – arvinjedcruz
    Jul 6, 2019 at 0:33
0

If your intention is to take the final character of the data in column A, then add the number, your best bet is to use a basic formula like:

RIGHT(A1,1)&"-"&TEXT(ROW(),"000")

The issue is if you're not using A1, just deduct the rows to reach 1 as the starting number. So if your data begins on row 10, the code should be:

RIGHT(A1,1)&"-"&TEXT(ROW()-9,"000")
Improve this answer
1
  • Just saw Seth's comment pointing out that you actually want a count of how many you have of the same type, as well as it seeming to number the item. This can be accomplished with a helper column which I can help with if you confirm this is what is required.
    – Gray Roberts
    Jul 2, 2019 at 11:07
0

Place some value in A1 and 

x-001

in B1. Then in B2 enter:

=IF(A2="","",IF(LEFT(B1,1)="x","y"&RIGHT(B1,4),"x-"&TEXT(1*(RIGHT(B1,3)+1),"000")))

and copy downward:

enter image description here

As you see, the prefix in column B will ping-pong between x and y and the suffix will increment as desired.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .