Data Table from Multiple Tabs

hello I'm going to try and explain this the best I can. It might be long. I apologize.

I have Multiple customers and they each have their own Tab. Within the tab are their part numbers that also have information needed on multiple rows and multiple columns per customer. So in Column A1 is a small description "Part Number". Then the part numbers are filled in going down column A. Columns B has a number attached to the first part number the from column C to M are different pieces of info. For 1 part number you may use up to 10 rows but some columns may have blank cells. You don't need to fill in every cell in each row, sometimes its scattered in different rows and different columns. However, you do need the information to stay in its specific column. I need to get this information from each tab as I search these part numbers in another tab. I may only need certain part numbers and the info on different days as it'll be different every day. There will be duplicates as I search for these customers information. I need the duplicates deleted as well.

Right now I am currently using the formula below and its pulling all the info from one tab. I would like the info pulled from each customers tab if possible. I am afraid it'll become to much within that 1 tab and it also makes it tough to search if I need to make changes. After I generate my results I have a macro delete the duplicates and pull them into another tab. For some reason when this happens my second column shifts down one cell. All I need after deleting the duplicates and the part numbers in the first column is the second number to stay with the part number in column B and from C to M it doesn't matter. I just need the duplicate info gone.

=IFERROR(INDEX(ALL!B:B,AGGREGATE(15,6,ROW(ALL!$A$2:ALL!$A$99988)/(ALL!$A$2:ALL!$A$99988=$A$2),ROW(ALL!A1))),"")

Hopefully the attachment helps.


Please Help! Thanks in advance!!