Hello Sir/Madam,
In sheet1 I have a Column A contains systems such as 0-20-A0-P01, 0-20-A0-P02, P03,P04 and so on.
In other sheets2 in column A have same like with many duplicates. In same sheet 2 somewhere column D or column E have values like "A","B","C" with duplicates.
I need to do in sheet 1 vlookup formula and findout how many "A" available in respective of 0-20-A0-P01, And how many "B" with respective of 0-20-A0-P01 and so on.
same like other system 0-20-A0-P02 contains how many A, B, C and so on
I tried with help of vlookup and countif i couldnt . I need suggestion please
From Germany, Munich
In sheet1 I have a Column A contains systems such as 0-20-A0-P01, 0-20-A0-P02, P03,P04 and so on.
In other sheets2 in column A have same like with many duplicates. In same sheet 2 somewhere column D or column E have values like "A","B","C" with duplicates.
I need to do in sheet 1 vlookup formula and findout how many "A" available in respective of 0-20-A0-P01, And how many "B" with respective of 0-20-A0-P01 and so on.
same like other system 0-20-A0-P02 contains how many A, B, C and so on
I tried with help of vlookup and countif i couldnt . I need suggestion please
From Germany, Munich
Prakash, Seems like you are trying to work on wrong formula. If you want to Know how many A / B / C are with 0-20-A0-P01 and 0-20-A0-P02 so on. then stay in sheet 2 and insert pivot table. Select the range of column and columns containing A,B,C.
Remin the system numbers in "RowLabel" then move A,B,C into value table. then you will get the result like [U]this
Rowlabel A Count B Count C Count
0-20-A0-P01 2 1 3
0-20-A0-P02 3 4 1
0-20-A0-P03 1 3 2
Check the attachement for reference, please let me know if worked.
From Kuwait, Kuwait
Remin the system numbers in "RowLabel" then move A,B,C into value table. then you will get the result like [U]this
Rowlabel A Count B Count C Count
0-20-A0-P01 2 1 3
0-20-A0-P02 3 4 1
0-20-A0-P03 1 3 2
Check the attachement for reference, please let me know if worked.
From Kuwait, Kuwait
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.
Vlookup example.xls
Competency required level.doc