Find the previous variant value when the current row doesn't have a variant. In such cases, the previous variant should be used

Help Studio
, ,
1

Hi All,

So I have an Excel sheet which contains values, with each column representing an AM number. In each column, I need to find the positions of '0’s and '*'s and their corresponding variant values. If the variant is not present, I want to find the previous variant value when the current row doesn’t have a variant. In such cases, the previous variant should taken as current variant.

The image shows an Excel spreadsheet with a table highlighting two columns labeled "Variant" and "AMnumbers," containing numerical values and symbols, along with colored bars for emphasis. (Captioned by AI)
The image shows an Excel spreadsheet with a table highlighting two columns labeled "Variant" and "AMnumbers," containing numerical values and symbols, along with colored bars for emphasis. (Captioned by AI)1920×1004 66.3 KB

Need solution to find the previous variant.

Please find the attached excel for your reference

Function_G10000.xlsx (16.9 KB)

Thank you!

@Yoichi

2

Hi,

Can you also share expected result?

Regards,

3

Hi @Yoichi

Good Day!

I looking for Variant value

step1- will Go One Column Value after Other as you can see in table 001,005,010,…

The image shows a color-coded Excel spreadsheet with various numbers and asterisks organized in rows and columns. (Captioned by AI)
The image shows a color-coded Excel spreadsheet with various numbers and asterisks organized in rows and columns. (Captioned by AI)810×503 5.97 KB

For Example First value Is 001 in this column in this column we need to find the how may 0’s and *'s are present in That 001 Column

This image shows a spreadsheet with colorful borders, numerical entries, and asterisks scattered throughout various cells. (Captioned by AI)
This image shows a spreadsheet with colorful borders, numerical entries, and asterisks scattered throughout various cells. (Captioned by AI)810×503 6.01 KB

where above value we need to find variant so for 001 are 15 and 30

If the Variant is corresponded to same row we will consider directly if its not present we need to consider the nearest varient to the 0 or * value

The image shows a colorful spreadsheet with highlighted sections, including yellow and pink headers, containing numerical data, starred entries, and hand-drawn annotations encircling certain numbers. (Captioned by AI)
The image shows a colorful spreadsheet with highlighted sections, including yellow and pink headers, containing numerical data, starred entries, and hand-drawn annotations encircling certain numbers. (Captioned by AI)810×503 15.6 KB

so as output based on 0’s and *'s we need to find the variant numbers in that column(001)

For example Output

For 001 Column Variants are 15 & 30
For 005 Column Variants are 10, 15 & 40
For 010 Column Variants are 15
For O15 Column Variant are 30
For 020 Column Variants are 10

in this way i want variants for all column number

Sample File
VarientData.xlsx (15.1 KB)

@Yoichi
Thank you!

4

Could you please kindly help me on this Logic to get Variant Values

@Yoichi , Thank you In Advance

5

Hi,

How about the following?

image
image1088×2327 165 KB

image
image394×696 11.6 KB

Sample
Sample20250521-3.zip (17.9 KB)

Regards,

1 Like
Forum Engagement Daily Reports
6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.