How to get the same value of Preceeding column values with different values of another

[anureddyarumalla]

Hi,

I had a data which is related to my project. One column is having values like s1, s1.1, s1.2.... and s2, s2.2.......etc., my problem is that i need to get the value of preceding cell value with comparing to this column values. Like if s1 preceding cell value is 1 then i should get the same '1' to s1.1, s1.2....same as like s2.1, s2.2 value to be '30'.. Please help me in this issue. which function should i use to get those values.

[JohnTopley]

Please post a small file showing examples of required outcomes.

To post a file, click "Go Advanced" then "Manage Attachments"

[anureddyarumalla]

pls find sample

[JohnTopley]

Sorry but I still don't understand what you want: add manual examples (and post a smaller file).

[anureddyarumalla]

sir,
for s1 the preceding cell value is 1, so the disease number should be '1' and for s1.1 also the Disease number should be '1' only . same as for s2..etc. the continuous number should have the same value.

[JohnTopley]

In C2 and copy down

=IFERROR(INDEX($A$2:$A$30,MATCH(LEFT($B3,FIND(".",$B3)-1),$B$2:$B$30,0)),RIGHT(B3,LEN(B3)-1))

Assumed the "major" categories are in rows 2 to 30 of B

[anureddyarumalla]

thank you, can you explain how did you work on it.

[samba_ravi]

C2=

Please Login or Register  to view this content.

Try this and copy towards down

[JohnTopley]

=IFERROR(INDEX($A$2:$A$30,MATCH(LEFT($B3,FIND(".",$B3)-1),$B$2:$B$30,0)),RIGHT(B3,LEN(B3)-1))


The part of the formula in RED:

The MATCH uses the text prior to the first full stop to find the entry in rows B2:B30

For example if B3 contained S1.1.2

FIND returns the position of the first "." i.e 3 in this case, and we subtract 1 to get 2.

LEFT(B3,2) = "S1" i.e the first two characters from the left

MATCH then finds the corresponding value (of "S1") in column B giving the row number

INDEX then takes the corresponding value from Column A

If there is no full stop we get an error which where the IFERROR comes into play;

If there is an error, then get the number to the RIGHT of the text in B

If B3=S12 then RIGHT(B3,LEN(B3)-1)) subtracts 1 to from the length of the text i.e 3 -1 =2 so the results is 12 (rightmost 2 characters)

Hope this explains how it works.

[anureddyarumalla]

ya thank you.

[anureddyarumalla]

yes, mr. siva, this worked. thank you

[JohnTopley]

@siva,
Formula does not work for S3.3.1.1 or S3.4.3 or similar.