Copy data from non-adjacent cells to a summary sheets based on criteria

My boss had me create a spreadsheet to track Letters of Intent to Purchase specific glass types from three different vendors to guarantee pricing for our customers before a price increase goes into effect. The first sheet lists all the customer and job names in Column A & B respectively and then subsequent columns would list various glass types and the square footage of each used in that particular job.

What I want to do is then to have the data automatically sent to the correct vendor table. To clarify, the first sheet/table lists the jobs and the glass from all three vendors are possible choices. I want the other tables to list all the glass for that specific Vendor. Each job could possibly show up on all the vendor-specific tables depending on what they ordered for their job.

I have included a sample version of the data.

Letters of Intent.xlsx

The different glass types are produced by different vendors. If you look across column headings on sheet one "jobs" you will see 1/4" bronze, and SB60 temp and 62/27 temp. The other tables list only the glass types made by the vendor listed as the tab title "Vitro", "Pilkington", and "Guardian". What i was hoping to do is look and see if the columns under Guardian have non-blank values in the same labeled columns in "jobs" then the values and the associated customer name and job name are copied over as well. This would then be done for Pilkington and Vitro as well. Does this make sense?

I need to update the columns, because I used this same spreadsheet last year, but only listed the glass types that were used in the various jobs. I moved it all to the other tables manually last year and just wanted it automated this time.

Thanks for looking at my question Carim.

As the Letters of Intent come in and I see what they are going to use I list more glass types on the jobs sheet. I have not added all the new ones to the vendor sheets yet because this was just a sample to see what is done. If you can make it expand to fit new data as it its added it would be wonderful. I have not had a chance to look at your test yet so I will do that, but basically what I would be doing is this.

Look at Guardian sheet to see what glass is manufactured by them. Then using that info look at the jobs sheet and see what jobs use one of those products and then copy the customer and job names to columns A & B in guardian sheet and the values for square footage in the appropriate intersection of "customer/job" row and "glass type" column.

So I would see that Guardian makes 43, 54, 62/27, 68, etc in annealed and tempered and then look at the job sheet and see for example that row 17 "Cartney Park and Shop uses 62/27 annealed and tempered so We would copy those names to the A&B columns of the Guardian sheet and the sqft 2200 and 1500 respectively to the column under those headings in Guardian sheet. However, this same job uses satin and clear so I would need to put the names and values on the appropriate cells on the correct sheets following the same logic.

Carim,

Thank you for all the help. The Vendor tables are abbreviated in the sample I sent just to make it easier. Each Vendor's selection of products is set. It is the choice of glass for each job that is flexible.

Would you make a list of all products and their corresponding Vendors and then use this table as a key for an XLOOKUP or MATCH function? I'm new to this, but I couldn't figure out how to get it to send the sqft values to the Vendor-specific tables. I could pull the names, but not the values.

Could you use something like NOT(ISBLANK()) to find cells with values and then check what column it is in (using the column label) and then use the Vendor Product table to determine which Vendor-specific table the sqft should go to?

Carim,

I'm sorry I haven't made this clear and I appreciate your patience. The vendor is not noted specifically. Let me explain the process and maybe that will help.

As I said before, we have 3 major vendors: Guardian, Pilkington, and Vitro. Each of these vendors has their own specific products that they make. Pilkington is mainly the clear of different thickness and the tints (gray, bronze, etc). Vitro products are usually prefixed by SB (for solarban) and then a number (eg: SB60 or SB90). Guardian products are typically SN, SNR, SNX and then a number (eg: SN68, SNX62/27). That is why I set up the Guardian, Vitro, and Pilkington tables.

So now that the vendors are going to increase their prices we have had our customers tell us of jobs they will doing in the near future so we can get the vendors to guarantee the old pricing for these jobs.

So they write a letter that gives us their name and the job name and then the glass type(s) and the number of sqft they will be using of that type. I then take that information and put it into the Jobs table so we have a running total of all of the glass types and sqft.

Last year I went in manually and looked for all the jobs using Guardian glass and put the correct values on the Guardian table and did the same for the Vitro and Pilkington glass as well.

What I am trying to do this year is have Excel do what I did manually last year. That is I want it to look at the Jobs table and see, for example, 600 sqft listed in the SB60 temp column for job "x". It would then copy customer name and job name from that row to the Vitro table and the 600 to the correct column on the Vitro table. It would then do that with the next non-blank column and add it to the row created in the vitro table already from the previous 600 or create a new row in the Pilkington table if it is 2000 sqft of 1/4" gray.

Does that make sense? I can't assign Vendor to the jobs themselves because they may have 3 glass types from one vendor or 1 from each of the 3 vendors depending on what the customer and the architect decide and send to me in the Letter of Intent. I hope this makes sense.

Then I would like it to look if I have added any new jobs (rows) and extract the information to the correct vendor table.

I was wondering if I could use something like a combination of NOT(ISBLANK()) to look for columns with values in them in Jobs table and then find what column they are and use the label of the column and something like XLOOKUP with the Lookup Values being the column titles from each vendor table to see what table that would be sent to and and then copy the values appropriately.

So in my mind I would have Guardian table do XLOOKUP with its column labels and look at Jobs table to find where there are sqft values in columns with the same names. Then it would copy the job name and customer name and sqft as noted above. However, I couldn't figure out how to do this. I hope this long explanation helps you to understand my goal.

How would you do this? I have to have the list of all the jobs as in Jobs table. I also have to have them divided by vendors so I can send the totals to the respective Vendor.