Plot blank along with matches in the vertical columns

[Sekars]

Hello Friends

I need to plot blank for the missing matches from the input cells B8:C54 to light yellow cells G8:H38 by formula.

thanks in advance

[Bo_Ry]

Please try

G8 Press Ctrl+Shift+Enter copy to H8 and copy down

Please Login or Register  to view this content.

[Sekars]

Hello Bo_Ry

Thanks for your formula, it is really helpful, again 1 need one change, is there any way to do the formulas workable if we inserted some columns between the column B and C.

Thank you once again

[Bo_Ry]

Yes, if source data still 2 columns and colums in between is blank then just minor modify.
But if there any data between that 2 columns, it will be a lot harder.
Please upload your files.

FYI. now I'm on vacation untill Monday, with no access to PC.

[Sekars]

Hello BO

Unfortunately the intermediate column cells will have the texts not blank cells. I have uploaded the new excel file.

Sorry i should attach this earlier, if required i will raise a new thread.

thanks in advance

[Bo_Ry]

Hi Sekars,

L8 with CSE and copy down

Please Login or Register  to view this content.

S8 CSE and copy down

Please Login or Register  to view this content.

[Sekars]

Hello Bo_Ry

Thanks for your formulas.

Again i change, because of huge usage of array formulas i tried to use the Post 2 file and formulas given by you.

Now there is no intermediate columns between 2 input columns. But there may be is some intermediate text filled columns between 2 output columns, not sure.

Here if i inserted the rows to top and columns to left the array formulas does not shows the result.

I hope you know the trick to make the formulas work.

Kindly elaborate how to make the formulas usable when if we are adding the rows to top and columns to left or inbetween input and output and if required columns have text filled between the output columns.

thanks again

[Bo_Ry]

The formula is too complicated for insert column and dynamic change.

My best try is Column V8 CSE and copy down, No text between that 2 columns.

Please Login or Register  to view this content.

H8 and J8 is simple Vlookup


Otherwise, you maybe need VBA to get unique list from 2 columns, the rest will be simple Vlookup.

[Sekars]

Hello Bo

Thanks thankyou, this is what i required.

Sorry for my late reply and not plotting the requirement. I am not preferred to use VBA and Macro will use your formulas. Because of reduction of array formula (Still array formula is there) my worksheet is calculating in quick time / more speed.

Thank you.

[Bo_Ry]

Hello Sekars,

Thanks for many reps.
Add add more criteria, now you can fill text in between columns.

V8

Please Login or Register  to view this content.