Possible to have a sheet automatically built from another sheet using a formula?

tgattsh

This is probably not possible, but this group is amazing and may have other ideas. I have an ORDER sheet that list various products and services (ITEM DESCRIPTION) into 5 different MARKET(s). This could be 100's or 1,000's of line items with many duplicates. Is it possible to write a formula that would go through each market looking for the different products within those markets and create a list while not duplicating the items?

Gillian C

Hi @tgattsh

What about creating a column in your original sheet that finds the first instance of each item in a market. Then use that to drive a 'Copy Rows' automation to the new sheet. You would then be able to hide the columns you don't need and only show the relevant ones? Then you would have a list of every first occurrance?

The formula for the first instance is a cell formula

=COUNTIFS(Market$1:Market@row, Market@row, [Item Description]$1:[Item Description]@row, [Item Description]@row)

Then drag it to the bottom of your current entries. As long as the formula is in two rows above your current row, when you add another row the formula automatically copies down to the new row?

It's a possible way

Danielle Wilson

@tgattsh If you wanted a sheet per market (for example), you could create a different sheet per market and create different reports per market off your original sheet to filter down, then DataMesh that report data onto your new market sheet with the option selected that only picks the first match for duplicates, so you wouldn't get any duplicates.