View Full Version : Need help to solve this critical scenario
bsync
03-04-2010, 07:39 AM
Hi Friends,
Pls help me to solve the below mentioned scenario
Scenario is mentioned in the attachment
Thank you
mbarron
03-04-2010, 08:25 AM
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
03-04-2010, 08:37 AM
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
03-04-2010, 08:44 AM
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
03-04-2010, 08:46 AM
B is the column betwee A and B and it may be an empty column
Bob Phillips
03-04-2010, 08:47 AM
Why, what is wrong with the formulae?
bsync
03-04-2010, 08:50 AM
I want this result to be used in other sheets linked to this sheet
Bob Phillips
03-04-2010, 09:23 AM
You can do that with formulae.
bsync
03-04-2010, 09:25 AM
ok can u show an example
mbarron
03-04-2010, 09:40 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.