Adding and modifying new rows based off existing data

[Safri15]

Hi All

Attached is a list of Customer Discounts (Selling1) determing by a catergory (CATCODE).

There are 5 catergories in this example (ABC170-174).
I need to ensure every account has all 5 catergories, where if any are missing it follows the below rule: -

ABC170 = A (This will already exist for all accounts)
ABC171 = B (If already populated, A if not)*
ABC172 = A
ABC173 = A
ABC174 = 0

*If ABC171 isn't already populated, it can match ABC170, i.e. = A
However if there is already a value attributed to this, then must remain as it is, i.e = B

ABC172 & 173 needs to match ABC170
ABC174 must be 0 and already is, so for this exercise can be omitted.

This is just one example, I have around 30x alpha-numeric prefixes which all contain approx 5 numeric suffixes each. I hope that once this category is completed I would be able to modify the rule on a case by case basis to suit each alpha-numeric prefix myself. The actual data has 4000 individual customers so far too labourous to do manually.

So an end result should appear roughly like this for each account (but obviously no issue for new entries to be added to the bottom of the worksheet): -
CUST1 ABC170 30
CUST1 ABC171 25
CUST1 ABC172 30
CUST1 ABC173 30
CUST1 ABC174 0

Hope this all makes sense, any questions please let me know. And thank you in advance.

[Squeaky]

Hi Safri15.
If I understood correctly, here is my solution that I will need to walk you through. What I see is your data is condensed, that is each customer does not show all categories. You want to rebuild your chart to include all categories for all customers, then populate them according to your examples, unless there is already data there at which point you want the original data to populate.
So the first thing I did was add some formulas to your tab to make it easier to pull the current data. These are simple formulas, but will need to be pulled down the entire list. In D, the formula simply joins cell in A to cell B to create a lookup that is unique. E contains the existing information I want to pull into the new chart. You can hide this information later if you wish.
To build the chart, I had to take the formulas and create them in sets of 5 unique formulas, special to each category. These formulas will lookup original data and set the new categories up as well. Using your data that I copied over you can check the information easier. These rows (I-K) can be hidden.
In order to extend the customer list, you will need to highlight a set of 5 x 3 cells, such as all Columns, A, B, C, of CUST7. When you highlight them put your mouse over the bottom right corner so the mouse becomes a ‘+’, click and hold, and drag down as far as you need, BUT make sure you press the CONTROL key before you let go. Pressing the control key will set up a repeating pattern on the catcode formula, which is essential. If the data does not format correctly, ctrl-Z and try again. Try it with and without holding the ctr key if no joy. (I left off CUST8 – CUST10 so you can create them and check the data.)
To “Clean” the sheet you can copy/paste values, but remember they will eliminate all formulas so save a copy before then if you need. Once cleaned you can delete all of the added rows.
Squeaky.

[Safri15]

Hi Squeaky.

Thank you very much for the reply, you have given a perfect solution to phase 1 of the issue in creating the new records.
Phase 2 of the problem was making the results match the pattern.

I have managed to tackle this myslef now I know the trick of being able to CTRL+drag a block of data, a very useful trick to know.

Thank you very much for you help, it is much appreciated.