Vlookup across multiple tabs

[amlan009]

Dear Experts ,

This will really really save my life ,

I attach a file ,

In tab 1 (sheet 1 ) i have column F as DBC number .most rows have single DBC numbers(eg ABC ) ,but some have multiple dbc numbers ,eg (ABC,XYZ,ABC, etc )seperated by a comma ...in column H ,i have gross amount for the DBC entry in column F ....if there are multiple DBC entries in column F ,then gross amount (single figure ) is sum of individual gross amounts of all DBC entries available in tab 2

In tab 2 i have comprehensive list of DBC in column A ,the corresponding invoice numbers in column I and the corresponding amounts in column F -note here every row has only single DBC number

Now,i want tab Final to be self generated doing the following ,


wherever there were multiple DBC entries in sheet 1 ,insert equal number of blank rows in the Final tab and then fill out seperate DBC in each row ..
let me give a example ,if i have multiple DBC entry in sheet 1 say - ABC ,XYZ ,EFG ...so there are 3 entries ...
so i will insert 3 blank rows ,first row where there was ABC ,XYZ ,EFG ,i will delete XYZ ,EFG and insert XYZ in second row and EFG in third Row .....for multiple DBC entries i wish that they be given a color shade if possible

For each of these DBC entries ,i will take from sheet 2 the invoice number and put it in column G .and outstanding amount in column H ........for multiple entries ,the total of the DBC will be calculated as listed out ...in case if invoice number is not found ,then NOT FOUND will be typed in cell invoice number to avoud macro from crashing and proceed to next !


The invoice numbers always form a pattern /xyz ...anything after the last / should appear in the name of the party column (column E ),


All columns E,G,H should be given a light shade as attachment if possible !

I know its a tough project but a interesting one ,will appreciate help ,



Thanks ,


Amlan Dutta

Note :Since noone has attempted it yet and i kinda need it quick ,i am posting it at http://www.ozgrid.com/forum/showthread.php?t=166992

[jaslake]

Hi Amlan
I'll look at it with you. May take a while...I have house guests until tomorrow.

[jaslake]

Hi Amlan

See if the attached suits your needs...let me know of issues.

Ah...I see you got a PAID FOR solution from your CROSS POST...my bad...should have looked...won't happen again.

[amlan009]

Sorry ,Jaslake ,having problems opening the site ,so the late revert ...dunno whether it is same for everyone else or just my ISP settings ,saw that you have solved the case ,your solution works awesome ....every solution is special ...since i am learning the subject .....
if there are others from india ,they can understand what i mean ,i simply was not able to log to the site ,but i am now checking your code to understand it throughly because it looks different but it works correctly and effectively with absolutely perfect output in first place
itself ....

please dont feel that this solution doesn't mean much to me ,it means the world to me ,i save all solutions in a folder which i keep studying in the night ,although this code looks a bit complex so it will take me more time to understand the code in totality ,

i attach the paid solution i got and i wish to pay the same to you also since this solution saves my job !

Most important ,like i mentioned in first thread that i don't wanna end up hurting anyone ,as a student i love many solutions and it's not your mistake ,i should have been the first to point out that i had a solution ,but i was kinda unable to link in the site (i was getting webpage not found )...i guess ISP problems,

I hope you will forgive me ,

Thanks for the post ,

I will mark the thread as solved and i will pray that i haven't landed up hurting you !

I am sorry ,if i did ,

i am trying to play with your code so that the Final sheet (generated using it gets placed after the last tab ,as of now it comes in between )rest i have managed the borders once i have managed the position of worksheet ,i will post it ....i feel very good ,you know when i make some revisions ,i feel thousand times wiser ,i know it's nothing in comparision to the wonderful codes you write !


Please accept my apologies ,
Humble regards ,

Amlan Dutta