Convert Table to List

Hi,

Following is a screen shot of a spreadsheet I use to identify parts on a machine.

Screenshot 1.PNG

The quantity of each individual part will vary between 1 and ~20.

At the moment I use a second table (see below) to turn the first table into a list of unique values in Column F, and then use a COUNTIF formula in Column G to determine the number of each part that I need to order.

Screenshot 2.PNG

The problem is populating Column F with every value from the original table, without duplicating any. At the moment I use conditional formatting to identify unique and duplicated values, but this is fairly clunky.

Can anyone suggest a better way of doing this?

Thanks.

Hi Ford,

Thanks for the response. I'll give your idea a shot tomorrow and see if it achieves my objective.

Attached is a sanitized spreadsheet. I need to convert the data in the top table into a list of parts with quantities in the bottom table.

Thanks again,

Shayne

Thanks JeteMc. Greatly appreciated.

Hi JeteMc,

I have transferred your forumulas to my actual spreadsheet, and have them working well with the formulas on the same worksheet as the data table.

I then tried transferring the formulas to a new worksheet (within the same spreadsheet) to give me more flexibility with formatting. I have corrected all of the references so they point to the data table on the original worksheet, but I'm having trouble getting them to work.

Are there any other adjustments I need to make in order for them to work when they are on a separate worksheet to the data table?

I have uploaded my current spreadsheet. Hoses and Fittings is the Data Table. Fittings is the sheet that I'm trying to set up with the formulas.

Regards,

Shayne