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
Vlook up won’t help, as it will pick the first A or B or C corresponding value in sheet 2, since you say there are duplicates. Try Pivot Table, i guess that will help resolve your problem.
Can you specify what is your main motive?
a) To get result i.e. counting how many A, B, C....
or b) To get result Only using Vlookup and countif?
If your motive is option (a), then find the attached excel sheet or else reply back. Then I would try to work on the option (b).
But I agree with nkumar11. Vlookup will give only the first encounter. You can also try Pivot Table as suggested by nkumar11.
Lets see if any experts can solve it using vlookup along with combination of any other functions..... coz excel is a vast area and any combination could give you a result !!!!!
From India, Gurgaon
1a) To get result i.e. counting how many A, B, C....
or b) To get result Only using Vlookup and countif?
If your motive is option (a), then find the attached excel sheet or else reply back. Then I would try to work on the option (b).
But I agree with nkumar11. Vlookup will give only the first encounter. You can also try Pivot Table as suggested by nkumar11.
Lets see if any experts can solve it using vlookup along with combination of any other functions..... coz excel is a vast area and any combination could give you a result !!!!!
From India, Gurgaon
HI Use countifs and then remove duplicates.find he excel sheet and let me know if there is any problem thanks
From India, Pune
From India, Pune
Dear All
I found the solution for my query by use of sumproduct, which is used to checking two arrays at spreadsheet. With the assist of one of my query. Anyhow all suggestion is also its most valuable for me in future update my work process. I would really appriciate to come forward to assist me. Thank you so much for all. If i get any query i ll let u know with clear pictures. Thank you once again
From Germany, Munich
I found the solution for my query by use of sumproduct, which is used to checking two arrays at spreadsheet. With the assist of one of my query. Anyhow all suggestion is also its most valuable for me in future update my work process. I would really appriciate to come forward to assist me. Thank you so much for all. If i get any query i ll let u know with clear pictures. Thank you once again
From Germany, Munich
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.
PF query.doc
PDF - Query.pdf