Forum Discussion
Solved
Using Countif to create a Lookup column
=D3&COUNTIF($D$2:D3,D3). I am using this formula to concatenate pipe size with its nth incidence. The formula works fine except when reading numbers such as 10 or 20 that end with a zero.
The issue with your formula arises when counting occurrences of pipe sizes ending in zero. The COUNTIF function considers numbers and text identically, leading to miscounting for values like "10" or "20". Here's a corrected formula that addresses this:
Excel=D3 & TEXT(COUNTIF($D$2:D3, LEFT(D3, LEN(D3)-1) & "*"), "0")
Explanation:
- LEFT(D3, LEN(D3)-1): Extracts all characters except the last digit from D3. For "10", it returns "1".
- "*": Appends a wildcard character to the extracted prefix, making the pattern "1*".
- COUNTIF($D$2:D3, LEFT(D3, LEN(D3)-1) & "*"): Counts the occurrences of pipe sizes that start with the characters in D3 followed by any digit using the wildcard.
- TEXT(..., "0"): Converts the count to text format, ensuring leading zeros are preserved (e.g., "1" instead of "1").
- &: Concatenates the pipe size (D3) with the formatted count, resulting in the desired output (e.g., "10|1").
This formula effectively counts occurrences based on the pipe size's prefix, excluding the trailing zero, and displays the count with leading zeros when necessary.
smylbugti222gmailcom =IF(D25=0,"",D25 & TEXT(COUNTIF('90 DEG NONREPAD NOZZLE WELDS'!$D$2:D25, LEFT(D25, LEN(D25)-1) & "*"), "0")) Copying the formula produced these results
1"1, 1"2, 10"1, 1"4. With this formula the number ten is being treated as a one. Do you have a solution for this?
I am building an entire set of templates for our estimating program. Each summary page that I build will perform a vlookup using the results of the formula you have provided. In the industrial world 10" pipe are common, and so it is critical that a 10 is treated as such instead of a 1 every time.