Comparison with rows and columns for one set of records possible !!!?????????

Hi All,

Good day. I would like to get a clarification or confirmation from expertise whether the below listed scenario is doable using any Excel formulae or not? Any leads or hints will help out.

I need to do a compare on the different column fields, then do compare between two different row fields in same column and pass the value of one cell field to next field if it satisfies. I will better explain this with the scenario which might make sense.

The initial report looks like listed below.

Please Login or Register  to view this content.

For every set of Col-A record, we will be having minimum 2 records (rows) one record with '000' and other with '00N' and so on. My requirement is I should check for the col-B with in unique value of Col-A.

For Example, consider the value '312' in Col-A having 4 records with 4 different values in Col-B. So we consider '000' as header records and Col-3 holds its description and Col-4 is Sub-description which will be filled with '---------------'. Coming to detailed records, rows with value '002','003' and '500' holds Col-3 value as header record description and corresponding sub-descriptions for their value in Col-4. So the output report should look like listed below for reference.

Please Login or Register  to view this content.

Please let me know if I am not clear with my requirements. Appreciate all your time and patience.

Thanks

Hi Lem,

Thanks for the quick response. Appreciate it. I tried your formula. But it works for the very first record (Header record) for every unique COL-A field value. For detail records, its not showing the actual required value. Instead it shows "#N/A". I tried correcting this "#N/A" by using TRIM or CLEAN. but it dint work out.

I am using your formula =IF(B2="000",C2,INDEX(C:C,MATCH(B2&A2,B:B&A:A,0))) in the requested field and not wrapping it inside {}. If I do so, the formula wont work as expected. May I date to know the necessity of using { }?

Kindly advice me. Thanks.

Hi Lem,

Attached the sample file for your review and reference. I tried the same and it dint work out for me. But thanks for the link you provided. It gave insight about array.

Hi lem,

I believe this was misunderstanding in our communication. I need a report as attached in this post with Col-I is valid.

sorry for the confusion caused.

Hi Lem,

Great. It works. Initially, I got #N/A error. Then corrected it using the excel help "How to correct a #N/A error in INDEX/MATCH functions". You also gave this info in your earlier reply. It took sometime for me to understand the usage of it. Before applying it, I changed the field type to text and it works as you mentioned in your latest reply.

Thanks for the information. I learnt about INDEX and MATCH today. Appreciate your patience and thanks for your time.