Excel 2007 : How to match two field with reference field?

[upmuthukumar]

Hello all,

Please check in attached xls.

Related invoice's DR amount equals CR amount, then both matched DR and CR should be displayed next to next. Unmatched rows should be colored and displayed at the end.

Is it possible to do in formula?

Please help to resolve this issue asap...

Thanks and Regards,
Muthukumar

[dilipandey]

Hi upmuthukumar,

Stimulation_1.xlsx
See the attached file where I have used pivot table to achieve the desired results.. unmatched data is moved to down.

Add data to your table and refresh the pivot to obtain the results.. thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

[upmuthukumar]

Hi DILIPandey,

Thanks for your update..

Is it possible to do in formula? Because I have to compare this output with another data..

Please correct me if i'm wrong..

Thanks and Regards,
Muthukumar

[dilipandey]

I guess pivot table is best fit here...
For formula, it would be little lengthy and difficult to achieve.. and also you need to have the invoices as ready references on which formulas can work. thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

[upmuthukumar]

Thanks DILIPandey for your update.

Which Excel Formula function I can use for my requirement(Related invoice#'s DR amount equals CR amount, then both matched DR and CR should be displayed next to next. Unmatched rows should be colored and displayed at the end. ).

Please guide me to resolve this issue..

Thanks and Regards,
Muthukumar

[dilipandey]

Hi Muthukumar,

You can use Lookup functions (Vlookup, lookup in combination with Match ) to obtain the desired result.. but I would not say "Go Ahead" for this approach


regards,
DILIPandey

<click on below 'star' if this helps>

[upmuthukumar]

HI DILIPandey,

I am really happy for your quick support and suggestion.

Thanks you very much.

Regards,
Muthukumar

[dilipandey]

You are welcome Muthukumar...

Please mark this thread as [SOLVED].. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>