Records found but unable to get proper result.

Records found but unable to get proper result.

Post by Sourabh Salgotr » Mon, 04 Jun 2012 19:08:59



Dear sir,
           i am using index/match formula. in my problem i am getting the
1st occurrence result only.
  Sr. No. Subject Code Subject/Paper code  1 BF 1 MATHEMATICS I          2
#NUM! #NUM!          3 #NUM! #NUM!          4 #NUM! #NUM!          5 #NUM!
#NUM!          6 #NUM! #NUM!          7 #NUM! #NUM!          8 #NUM! #NUM!
       9 #NUM! #NUM!

i want the output like this.

 Sr. No. Subject Code Subject/Paper code  1 BF 1 MATHEMATICS          2 BF 2
CHEMISTRY          3 BF 3 ENGLISH I          4 BF 4 ELECTRICAL TECHNOLOGY
       5 BF 5 MECHANICS          6 BF 6 INTRODUCTION TO MANUFACTURING
PROCESS          7 BF 2P CHEMISTRY PRACTICAL          8 BF 4P ELECTICAL
TECHNOLOGY PRACTICAL          9 BF 7 INDIAN CONSTITUTION AND ETHICS
--------------------------------------------------------------------------------

sample sheet attached

Thanks & Regards
Sourabh
Contact Numbers: +91-94630-49202

  FORM.xlsx
34K Download
 
 
 

Records found but unable to get proper result.

Post by Ahmed Hones » Mon, 04 Jun 2012 19:20:13


Hi,

Use Vlookup formula, I think it should help you.

Rgds,,,,


> Dear sir,
>            i am using index/match formula. in my problem i am getting the
> 1st occurrence result only.
>   Sr. No. Subject Code Subject/Paper code  1 BF 1 MATHEMATICS I          2
> #NUM! #NUM!          3 #NUM! #NUM!          4 #NUM! #NUM!          5 #NUM!
> #NUM!          6 #NUM! #NUM!          7 #NUM! #NUM!          8 #NUM! #NUM!
>          9 #NUM! #NUM!

> i want the output like this.

>  Sr. No. Subject Code Subject/Paper code  1 BF 1 MATHEMATICS          2 BF
> 2 CHEMISTRY          3 BF 3 ENGLISH I          4 BF 4 ELECTRICAL
> TECHNOLOGY          5 BF 5 MECHANICS          6 BF 6 INTRODUCTION TO
> MANUFACTURING PROCESS          7 BF 2P CHEMISTRY PRACTICAL          8 BF
> 4P ELECTICAL TECHNOLOGY PRACTICAL          9 BF 7 INDIAN CONSTITUTION AND
> ETHICS
> --------------------------------------------------------------------------------

> sample sheet attached

> Thanks & Regards
> Sourabh
> Contact Numbers: +91-94630-49202

>  --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


--
Ahmed Bawazir
*???? ??????*

 
 
 

Records found but unable to get proper result.

Post by dguillett » Mon, 04 Jun 2012 22:05:56


How about a nice macro? Change your formatting and use in macro enabled workbook

Sub filtercopy()
With Sheets("COURSES").UsedRange
  .AutoFilter Field:=1 'criteria IF needed
  .AutoFilter Field:=2
  .AutoFilter Field:=3, Criteria1:=Sheets("form").Range("n1")
  .Offset(1, 3).Resize(, 2).SpecialCells(xlVisible). _
   Copy Sheets("form").Range("d35")
End With
End Sub

Don Guillett
Microsoft MVP Excel
SalesAid Software

From: Sourabh Salgotra
Sent: Sunday, June 03, 2012 5:08 AM


Subject: $$Excel-Macros$$ Records found but unable to get proper result.

Dear sir,
           i am using index/match formula. in my problem i am getting the 1st occurrence result only.
      Sr. No. Subject Code Subject/Paper code
      1 BF 1 MATHEMATICS I        
      2 #NUM! #NUM!        
      3 #NUM! #NUM!        
      4 #NUM! #NUM!        
      5 #NUM! #NUM!        
      6 #NUM! #NUM!        
      7 #NUM! #NUM!        
      8 #NUM! #NUM!        
      9 #NUM! #NUM!        

i want the output like this.

      Sr. No. Subject Code Subject/Paper code
      1 BF 1 MATHEMATICS        
      2 BF 2 CHEMISTRY        
      3 BF 3 ENGLISH I        
      4 BF 4 ELECTRICAL TECHNOLOGY        
      5 BF 5 MECHANICS        
      6 BF 6 INTRODUCTION TO MANUFACTURING PROCESS        
      7 BF 2P CHEMISTRY PRACTICAL        
      8 BF 4P ELECTICAL TECHNOLOGY PRACTICAL        
      9 BF 7 INDIAN CONSTITUTION AND ETHICS        
--------------------------------------------------------------------------------

sample sheet attached

Thanks & Regards
Sourabh
Contact Numbers: +91-94630-49202

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------

 
 
 

Records found but unable to get proper result.

Post by Sourabh Salgotr » Tue, 05 Jun 2012 02:53:41


thanks for help.

plz provide index/atch solution also


>   How about a nice macro? Change your formatting and use in macro enabled
> workbook

> Sub filtercopy()
> With Sheets("COURSES").UsedRange
>   .AutoFilter Field:=1 'criteria IF needed
>   .AutoFilter Field:=2
>   .AutoFilter Field:=3, Criteria1:=Sheets("form").Range("n1")
>   .Offset(1, 3).Resize(, 2).SpecialCells(xlVisible). _
>    Copy Sheets("form").Range("d35")
> End With
> End Sub

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software


> *Sent:* Sunday, June 03, 2012 5:08 AM



> *Subject:* $$Excel-Macros$$ Records found but unable to get proper result.

> Dear sir,
>            i am using index/match formula. in my problem i am getting the
> 1st occurrence result only.
>     Sr. No. Subject Code Subject/Paper code 1 BF 1 MATHEMATICS I         2
> #NUM! #NUM!         3 #NUM! #NUM!         4 #NUM! #NUM!         5 #NUM!
> #NUM!         6 #NUM! #NUM!         7 #NUM! #NUM!         8 #NUM! #NUM!
>       9 #NUM! #NUM!

> i want the output like this.

>    Sr. No. Subject Code Subject/Paper code 1 BF 1 MATHEMATICS         2 BF
> 2 CHEMISTRY         3 BF 3 ENGLISH I         4 BF 4 ELECTRICAL TECHNOLOGY
>         5 BF 5 MECHANICS         6 BF 6 INTRODUCTION TO MANUFACTURING
> PROCESS         7 BF 2P CHEMISTRY PRACTICAL         8 BF 4P ELECTICAL
> TECHNOLOGY PRACTICAL         9 BF 7 INDIAN CONSTITUTION AND ETHICS
> --------------------------------------------------------------------------------

> sample sheet attached

> Thanks & Regards
> Sourabh
> Contact Numbers: +91-94630-49202

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


--
mujhay dukh is baat ka nahin kay meri zaat ko
muntashir karny walay haath tairy thy
mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko
samaitnay walay haath tairy na thy

--------------------------------------------------------------------------------

Thanks & Regards
Sourabh
Contact Numbers: +91-94630-49202
Website:http://adhurapyaar.co.cc

 
 
 

Records found but unable to get proper result.

Post by dguillett » Tue, 05 Jun 2012 02:55:13


I like my solution.

Don Guillett
Microsoft MVP Excel
SalesAid Software

From: Sourabh Salgotra
Sent: Sunday, June 03, 2012 12:53 PM

Subject: Re: $$Excel-Macros$$ Records found but unable to get proper result.

thanks for help.

plz provide index/atch solution also

  How about a nice macro? Change your formatting and use in macro enabled workbook

  Sub filtercopy()
  With Sheets("COURSES").UsedRange
    .AutoFilter Field:=1 'criteria IF needed
    .AutoFilter Field:=2
    .AutoFilter Field:=3, Criteria1:=Sheets("form").Range("n1")
    .Offset(1, 3).Resize(, 2).SpecialCells(xlVisible). _
     Copy Sheets("form").Range("d35")
  End With
  End Sub

  Don Guillett
  Microsoft MVP Excel
  SalesAid Software

  From: Sourabh Salgotra
  Sent: Sunday, June 03, 2012 5:08 AM


  Subject: $$Excel-Macros$$ Records found but unable to get proper result.

  Dear sir,
             i am using index/match formula. in my problem i am getting the 1st occurrence result only.
        Sr. No. Subject Code Subject/Paper code
        1 BF 1 MATHEMATICS I        
        2 #NUM! #NUM!        
        3 #NUM! #NUM!        
        4 #NUM! #NUM!        
        5 #NUM! #NUM!        
        6 #NUM! #NUM!        
        7 #NUM! #NUM!        
        8 #NUM! #NUM!        
        9 #NUM! #NUM!        

  i want the output like this.

        Sr. No. Subject Code Subject/Paper code
        1 BF 1 MATHEMATICS        
        2 BF 2 CHEMISTRY        
        3 BF 3 ENGLISH I        
        4 BF 4 ELECTRICAL TECHNOLOGY        
        5 BF 5 MECHANICS        
        6 BF 6 INTRODUCTION TO MANUFACTURING PROCESS        
        7 BF 2P CHEMISTRY PRACTICAL        
        8 BF 4P ELECTICAL TECHNOLOGY PRACTICAL        
        9 BF 7 INDIAN CONSTITUTION AND ETHICS        
  --------------------------------------------------------------------------------

  sample sheet attached

  Thanks & Regards
  Sourabh
  Contact Numbers: +91-94630-49202

  --
  FORUM RULES (986+ members already BANNED for violation)

  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

  2) Don't post a question in the thread of another member.

  3) Don't post questions regarding breaking or bypassing any security measure.

  4) Acknowledge the responses you receive, good or bad.

  5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

  NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

  ------------------------------------------------------------------------------------------------------


  --
  FORUM RULES (986+ members already BANNED for violation)

  1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

  2) Don't post a question in the thread of another member.

  3) Don't post questions regarding breaking or bypassing any security measure.

  4) Acknowledge the responses you receive, good or bad.

  5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

  NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

  ------------------------------------------------------------------------------------------------------


--
mujhay dukh is baat ka nahin kay meri zaat ko
muntashir karny walay haath tairy thy
mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko
samaitnay walay haath tairy na thy

--------------------------------------------------------------------------------

Thanks & Regards
Sourabh
Contact Numbers: +91-94630-49202
Website:http://adhurapyaar.co.cc

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------

 
 
 

Records found but unable to get proper result.

Post by pawel lupinsk » Tue, 05 Jun 2012 03:12:31


Don,

love it (great reply)? :)

Pawel

________________________________


Sent: Sunday, June 3, 2012 6:55 PM
Subject: Re: $$Excel-Macros$$ Records found but unable to get proper result.

I like my solution.
?
Don
Guillett
Microsoft MVP Excel
SalesAid
Software

From: Sourabh Salgotra
Sent: Sunday, June 03, 2012 12:53 PM

Subject: Re: $$Excel-Macros$$ Records found but unable to get proper
result.
? thanks
for help.
?
plz provide index/atch solution also

How about a nice macro? Change your formatting and use in macro enabled  workbook

>?
>Sub filtercopy()
>With Sheets("COURSES").UsedRange
>? .AutoFilter Field:=1 'criteria IF needed
>? .AutoFilter Field:=2
>? .AutoFilter Field:=3, Criteria1:=Sheets("form").Range("n1")
>? .Offset(1, 3).Resize(, 2).SpecialCells(xlVisible). _
>?? Copy Sheets("form").Range("d35")
>End With
>End Sub
>?
>?
>?
>Don Guillett
>Microsoft
  MVP Excel
>SalesAid Software

>From: Sourabh Salgotra
>Sent: Sunday, June 03, 2012 5:08 AM


>Subject: $$Excel-Macros$$ Records found but unable to get proper  result.
>?
>?
>Dear sir,
>?????????? i am using  index/match formula. in my problem i am getting the 1st occurrence result  only.
>Sr.  No. Subject  Code Subject/Paper code
>1 BF 1 MATHEMATICS  I ? ? ? ?
>2 #NUM! #NUM! ? ? ? ?
>3 #NUM! #NUM! ? ? ? ?
>4 #NUM! #NUM! ? ? ? ?
>5 #NUM! #NUM! ? ? ? ?
>6 #NUM! #NUM! ? ? ? ?
>7 #NUM! #NUM! ? ? ? ?
>8 #NUM! #NUM! ? ? ? ?
>9 #NUM! #NUM! ? ? ? ?
>?
>?
>i want the output like this.
>?
>Sr.  No. Subject  Code Subject/Paper code
>1 BF 1 MATHEMATICS ? ? ? ?
>2 BF 2 CHEMISTRY ? ? ? ?
>3 BF 3 ENGLISH  I ? ? ? ?
>4 BF 4 ELECTRICAL  TECHNOLOGY ? ? ? ?
>5 BF 5 MECHANICS ? ? ? ?
>6 BF 6 INTRODUCTION  TO MANUFACTURING PROCESS ? ? ? ?
>7 BF 2P CHEMISTRY  PRACTICAL ? ? ? ?
>8 BF 4P ELECTICAL  TECHNOLOGY PRACTICAL ? ? ? ?
>9 BF 7 INDIAN  CONSTITUTION AND ETHICS ? ? ? ? --------------------------------------------------------------------------------

>sample
  sheet attached

>Thanks & Regards
>Sourabh
>Contact Numbers: +91-94630-49202

>--
>FORUM RULES (986+

  members already BANNED for violation)
Quote:>?
>1) Use concise, accurate

  thread titles. Poor thread titles, like Please Help, Urgent, Need Help,
  Formula Problem, Code Problem, and Need Advice will not get quick attention or
  may not be answered.
Quote:>?
>2) Don't post a question in the thread of
  another member.
>?
>3) Don't post questions regarding breaking or

  bypassing any security measure.
Quote:>?
>4) Acknowledge the responses you

  receive, good or bad.
Quote:>?
>5) Cross-promotion of, or links to, forums

  competitive to this forum in signatures are prohibited.
Quote:>?
>NOTE :

  Don't ever post personal or confidential data in a workbook. Forum owners and
  members are not responsible for any
  loss.
Quote:>?
>------------------------------------------------------------------------------------------------------
>To


Quote:>?
>To unsubscribe,


Quote:>--
>FORUM RULES (986+ members already BANNED for
  violation)
>?
>1) Use concise, accurate thread titles. Poor thread

  titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem,
  and Need Advice will not get quick attention or may not be
  answered.
Quote:>?
>2) Don't post a question in the thread of another
  member.
>?
>3) Don't post questions regarding breaking or bypassing

  any security measure.
Quote:>?
>4) Acknowledge the responses you receive,
  good or bad.
>?
>5) Cross-promotion of, or links to, forums

  competitive to this forum in signatures are prohibited.
Quote:>?
>NOTE :

  Don't ever post personal or confidential data in a workbook. Forum owners and
  members are not responsible for any
  loss.
Quote:>?
>------------------------------------------------------------------------------------------------------
>To


Quote:>?
>To unsubscribe,


?--
mujhay dukh is baat ka nahin kay meri zaat
ko
muntashir karny walay haath tairy thy
mujhay dukh faqt is baat ka hay
meri raiza raiza zaat ko
samaitnay walay haath tairy na
thy

--------------------------------------------------------------------------------

Thanks
& Regards
Sourabh
Contact Numbers: +91-94630-49202
Website:http://adhurapyaar.co.cc

--
FORUM RULES
(986+ members already BANNED for violation)
?
1) Use concise,
accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help,
Formula Problem, Code Problem, and Need Advice will not get quick attention or
may not be answered.
?
2) Don't post a question in the thread of
another member.
?
3) Don't post questions regarding breaking or
bypassing any security measure.
?
4) Acknowledge the responses you
receive, good or bad.
?
5) Cross-promotion of, or links to, forums
competitive to this forum in signatures are prohibited.
?
NOTE :
Don't ever post personal or confidential data in a workbook. Forum owners and
members are not responsible for any
loss.
?
------------------------------------------------------------------------------------------------------
To

?
To
unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)
?
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.
?
2) Don't post a question in the thread of another member.
?
3) Don't post questions regarding breaking or bypassing any security measure.
?
4) Acknowledge the responses you receive, good or bad.
?
5)  Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.
?
NOTE  : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
?
------------------------------------------------------------------------------------------------------

?

 
 
 

Records found but unable to get proper result.

Post by Sourabh Salgotr » Wed, 06 Jun 2012 00:18:19


Dear Sir,

           i have little knowledge about vba. that's why i am asking sol
with index/match.

and plz tell me abt offset function also.


> Don,

> love it (great reply)  :)

> Pawel

>   ------------------------------


> *Sent:* Sunday, June 3, 2012 6:55 PM

> *Subject:* Re: $$Excel-Macros$$ Records found but unable to get proper
> result.

>   I like my solution.

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software


> *Sent:* Sunday, June 03, 2012 12:53 PM

> *Subject:* Re: $$Excel-Macros$$ Records found but unable to get proper
> result.

> thanks for help.

> plz provide index/atch solution also


>   How about a nice macro? Change your formatting and use in macro enabled
> workbook

> Sub filtercopy()
> With Sheets("COURSES").UsedRange
>   .AutoFilter Field:=1 'criteria IF needed
>   .AutoFilter Field:=2
>   .AutoFilter Field:=3, Criteria1:=Sheets("form").Range("n1")
>   .Offset(1, 3).Resize(, 2).SpecialCells(xlVisible). _
>    Copy Sheets("form").Range("d35")
> End With
> End Sub

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software


> *Sent:* Sunday, June 03, 2012 5:08 AM



> *Subject:* $$Excel-Macros$$ Records found but unable to get proper result.

> Dear sir,
>            i am using index/match formula. in my problem i am getting the
> 1st occurrence result only.
>     Sr. No. Subject Code Subject/Paper code 1 BF 1 MATHEMATICS I         2
> #NUM! #NUM!         3 #NUM! #NUM!         4 #NUM! #NUM!         5 #NUM!
> #NUM!         6 #NUM! #NUM!         7 #NUM! #NUM!         8 #NUM! #NUM!
>       9 #NUM! #NUM!

> i want the output like this.

>    Sr. No. Subject Code Subject/Paper code 1 BF 1 MATHEMATICS         2 BF
> 2 CHEMISTRY         3 BF 3 ENGLISH I         4 BF 4 ELECTRICAL TECHNOLOGY
>         5 BF 5 MECHANICS         6 BF 6 INTRODUCTION TO MANUFACTURING
> PROCESS         7 BF 2P CHEMISTRY PRACTICAL         8 BF 4P ELECTICAL
> TECHNOLOGY PRACTICAL         9 BF 7 INDIAN CONSTITUTION AND ETHICS
> --------------------------------------------------------------------------------

> sample sheet attached

> Thanks & Regards
> Sourabh
> Contact Numbers: +91-94630-49202

 
 
 

Records found but unable to get proper result.

Post by ashish kou » Wed, 06 Jun 2012 00:29:43


see if it helps


> Dear Sir,

>            i have little knowledge about vba. that's why i am asking sol
> with index/match.

> and plz tell me abt offset function also.


>> Don,

>> love it (great reply)  :)

>> Pawel

>>   ------------------------------


>> *Sent:* Sunday, June 3, 2012 6:55 PM

>> *Subject:* Re: $$Excel-Macros$$ Records found but unable to get proper
>> result.

>>   I like my solution.

>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software


>> *Sent:* Sunday, June 03, 2012 12:53 PM

>> *Subject:* Re: $$Excel-Macros$$ Records found but unable to get proper
>> result.

>> thanks for help.

>> plz provide index/atch solution also


>>   How about a nice macro? Change your formatting and use in macro
>> enabled workbook

>> Sub filtercopy()
>> With Sheets("COURSES").UsedRange
>>   .AutoFilter Field:=1 'criteria IF needed
>>   .AutoFilter Field:=2
>>   .AutoFilter Field:=3, Criteria1:=Sheets("form").Range("n1")
>>   .Offset(1, 3).Resize(, 2).SpecialCells(xlVisible). _
>>    Copy Sheets("form").Range("d35")
>> End With
>> End Sub

>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software


>> *Sent:* Sunday, June 03, 2012 5:08 AM



>> *Subject:* $$Excel-Macros$$ Records found but unable to get proper
>> result.

>> Dear sir,
>>            i am using index/match formula. in my problem i am getting the
>> 1st occurrence result only.
>>     Sr. No. Subject Code Subject/Paper code 1 BF 1 MATHEMATICS I
>> 2 #NUM! #NUM!         3 #NUM! #NUM!         4 #NUM! #NUM!         5 #NUM!
>> #NUM!         6 #NUM! #NUM!         7 #NUM! #NUM!         8 #NUM! #NUM!
>>       9 #NUM! #NUM!

>> i want the output like this.

>>    Sr. No. Subject Code Subject/Paper code 1 BF 1 MATHEMATICS         2 BF
>> 2 CHEMISTRY         3 BF 3 ENGLISH I         4 BF 4 ELECTRICAL TECHNOLOGY
>>         5 BF 5 MECHANICS         6 BF 6 INTRODUCTION TO MANUFACTURING
>> PROCESS         7 BF 2P CHEMISTRY PRACTICAL         8 BF 4P ELECTICAL
>> TECHNOLOGY PRACTICAL         9 BF 7 INDIAN CONSTITUTION AND ETHICS

>> --------------------------------------------------------------------------------

>> sample sheet attached

>> Thanks & Regards
>> Sourabh
>> Contact Numbers: +91-94630-49202

>>   --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
*http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>

P Before printing, think about the environment.

  FORM (2).xlsx
34K Download
 
 
 

Records found but unable to get proper result.

Post by Sourabh Salgotr » Wed, 06 Jun 2012 17:17:08


many many thanks ashish sir for helping me. i am also done it with the
following formula

=IFERROR(INDEX(Table1[SUBCODE],SMALL(IF(COURSES!$A$2:$A$804=LIST!$E$1,ROW(INDIRECT("1:803")),""),ROW()-34)),"")

but sir your method is simpler than me now i am following your method.


> see if it helps


>> Dear Sir,

>>            i have little knowledge about vba. that's why i am asking sol
>> with index/match.

>> and plz tell me abt offset function also.


>>> Don,

>>> love it (great reply)  :)

>>> Pawel

>>>   ------------------------------


>>> *Sent:* Sunday, June 3, 2012 6:55 PM

>>> *Subject:* Re: $$Excel-Macros$$ Records found but unable to get proper
>>> result.

>>>   I like my solution.

>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software


>>> *Sent:* Sunday, June 03, 2012 12:53 PM

>>> *Subject:* Re: $$Excel-Macros$$ Records found but unable to get proper
>>> result.

>>> thanks for help.

>>> plz provide index/atch solution also


>>>   How about a nice macro? Change your formatting and use in macro
>>> enabled workbook

>>> Sub filtercopy()
>>> With Sheets("COURSES").UsedRange
>>>   .AutoFilter Field:=1 'criteria IF needed
>>>   .AutoFilter Field:=2
>>>   .AutoFilter Field:=3, Criteria1:=Sheets("form").Range("n1")
>>>   .Offset(1, 3).Resize(, 2).SpecialCells(xlVisible). _
>>>    Copy Sheets("form").Range("d35")
>>> End With
>>> End Sub

>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software


>>> *Sent:* Sunday, June 03, 2012 5:08 AM



>>> *Subject:* $$Excel-Macros$$ Records found but unable to get proper
>>> result.

>>> Dear sir,
>>>            i am using index/match formula. in my problem i am getting
>>> the 1st occurrence result only.
>>>     Sr. No. Subject Code Subject/Paper code 1 BF 1 MATHEMATICS I
>>> 2 #NUM! #NUM!         3 #NUM! #NUM!         4 #NUM! #NUM!         5
>>> #NUM! #NUM!         6 #NUM! #NUM!         7 #NUM! #NUM!         8 #NUM!
>>> #NUM!         9 #NUM! #NUM!

>>> i want the output like this.

>>>    Sr. No. Subject Code Subject/Paper code 1 BF 1 MATHEMATICS         2 BF
>>> 2 CHEMISTRY         3 BF 3 ENGLISH I         4 BF 4 ELECTRICAL
>>> TECHNOLOGY         5 BF 5 MECHANICS         6 BF 6 INTRODUCTION TO
>>> MANUFACTURING PROCESS         7 BF 2P CHEMISTRY PRACTICAL         8 BF
>>> 4P ELECTICAL TECHNOLOGY PRACTICAL         9 BF 7 INDIAN CONSTITUTION
>>> AND ETHICS
>>> --------------------------------------------------------------------------------

>>> sample sheet attached

>>> Thanks & Regards
>>> Sourabh
>>> Contact Numbers: +91-94630-49202

 
 
 

1. vlookup a record then extract three fields from the found record

I was recently given an excel spreadsheet of 6400 employee mail
records and asked to lookup their employee numbers in a 18000 line HR
file.   Vlookup handled the lookup "pretty well" except for the
multiple "John Smith",s etc.   My question is not about the duplicates
for now, but how would be the best way to pickup multiple fields from
the HR records.    In ignorance, I used a vlookup for ID in Col A,
another vlookup for EMP status in col B, and another for location in
col C.  I believe that I caused excel to search the HR records three
times for each email record.

I couldn't find a way to vlookup once then somehow use the row
location of the HIT to access the other fields.

I suspect I am using the wrong function but couldn't find a better
one.   I don't know how to use VBA so am looking for an excel
solution.

Thanks for any help.

2. Dragon NSp 5 RAM requirements

3. Help: No proper Scan result on Dysan/Memorex anymore !!

4. link error using c2pstrcpy

5. Help: No proper Scan result on Dysan 3D/Memorex

6. cant add anymore users

7. getting procedures to use proper color index via LOADCT, x

8. NUMBER OF MCSD's????

9. getting proper query_string with CGI module

10. Please help me find proper adapter for Visioneer 8600

11. Exceptions - finding proper excpt. handler

12. Unable to record TCP/IP in a 3 Tier Client Server application

13. unable to record controls inside java Applet