Need help to solve this critical scenario

[bsync]

Hi Friends,

Pls help me to solve the below mentioned scenario

Scenario is mentioned in the attachment

Thank you

[mbarron]

Assumes the A and B columns are sorted Ascending:

=INDEX($A$2:$B$24,MATCH(A28,$A$2:$A$24,0),2) & " to " & INDEX($A$2:$B$24,MATCH(A28,$A$2:$A$24,1),2)

[Bob Phillips]

Here is an array formula that doesn't require them to be sorted

="IB "&MIN(--IF(A2:A24=A28,SUBSTITUTE(B2:B24,"IB ",""),99^99))&" to IB "&MAX(--IF(A2:A24=A28,SUBSTITUTE(B2:B24,"IB ",""),0))

[bsync]

Friends,

Could you provide me a vba macro scipt for this scenario

Also Pls consider this case:

The "REM" column may or may not contain duplicates

A B C
REM001 IB -001
REM002 IB -002
REM002 IB -003
REM002 IB -004
REM002 IB -005
REM002 IB -006
REM002 IB -007
REM002 IB -008
REM002 IB -009
REM002 IB -010
REM011 IB -011
REM012 IB -012
REM013 IB -013
REM014 IB -014
REM015 IB -015



Expected

A B C
REM001 IB -001
REM002 IB -002 to IB -010
REM011 IB -011
REM012 IB -012
REM013 IB -013
REM014 IB -014
REM015 IB -015

[bsync]

B is the column betwee A and B and it may be an empty column

[Bob Phillips]

Why, what is wrong with the formulae?

[bsync]

I want this result to be used in other sheets linked to this sheet

[Bob Phillips]

You can do that with formulae.

[bsync]

ok can u show an example

[mbarron]

You can use the formula (it would have to be updated to point to another workbook).

=IF(MIN(--IF(A2:A24=A28,SUBSTITUTE(B2:B24,"IB","")
,99^99))=MAX(--IF(A2:A24=A28,SUBSTITUTE(B2:B24,"IB ",""),0)),"IB "
&MAX(--IF(A2:A24=A28,SUBSTITUTE(B2:B24,"IB ",""),0)),"IB
"&MIN(--IF(A2:A24=A28,SUBSTITUTE(B2:B24,"IB ",""),99^99))&" to IB
"&MAX(--IF(A2:A24=A28,SUBSTITUTE(B2:B24,"IB ",""),0)))

Paste the formula into a text editor and delete the line feeds at the end of each line.I added the line feeds to eliminate the need to scroll here in VBAX


Formula referencing the same book:
=A1 or
=Sheet2!A1

Same formula referencing another book.
='C:\Test\Day 01\[Test.xls]Sheet1'!A1