Excel transform data

Question

Hi,

I have two tables; I've created simple example

And where I have the number 1 I want to use the value in the top table to multiply the units;

so 20 * 0.2 * 0.3, so I created this off to the side;

IF( AND( Table1[[#Headers],[T1]]=Table2[[#Headers],[T1]],Table1[@T1]=1),Table2[T1],"")

Which I then pasted in place of the 1s and 0s, and used Product ( IF( Range<>0,Range))

But is there a better more dynamic way to get the result, can I've been trying to replace the values for each column with the correct decimal, Just to add I want this in excel not power pivot,
which I have used by unpivoting and then getting the correct figure by a lookup.

Richard.

Answer 1

Hi,

Another approach with formula. Try this formula in cell G5:

=B5:B12 * MMULT(C5:E12, TRANSPOSE(C2:E2))

HTH.

Answer 2

Hi,

This may be what you need. I am assuming your setup starts in A1.

1. Select the range C5:D11
2. Press Ctrl+F, enter 1 in the Find what box and click Find All:

3. In the Find and Replace dialog box the first entry will be selected, hold down the Shift key and scroll down and click the last entry (Book1 Sheet2 $E$11 1) in the above picture.
4. Click Close. (All the cells with 1's will be selected.)
5. Note the active cell (E11 in my case) Type =E$2
6. Press Ctrl+Enter.

Answer 3

Hi Richard

Please,

Try the formula =[@Units]*SUMPRODUCT(Table1,Table2[@[T1]:[T3]])

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 4

Hello RichardDrake1505,

I’m Ajibola, an Independent Advisor and Microsoft user like you. Thanks for posting the query here in this forum.

If I understand correctly, you have two tables in Excel, and you want to multiply the units in the top table with the corresponding values in the second table. You have created an IF statement to check the values and replace them with the correct decimals.

To achieve a more dynamic solution, you can use the INDEX and MATCH functions to look up and retrieve the corresponding decimal values from the second table. Here's how you can do it:

1. Assuming the first table is in cells A1:D4, and the second table is in cells F1:G3, enter the following formula in cell B2 of the first table:

=IF(A2=1, A$1 \* INDEX($G$2:$G$3, MATCH($A2, $F$2:$F$3, 0)), "")

2. Copy the formula in cell B2 and paste it to the remaining cells in the first table (B2:D4).

This formula uses the INDEX and MATCH functions to retrieve the corresponding decimal values from the second table based on the matching T1 values. It then multiplies the decimal value with the units in the first table. If the T1 value is not 1, it returns an empty string ("").

This approach allows for a more dynamic and scalable solution, as the values will be automatically looked up and multiplied based on the corresponding T1 values in the second table. You can adjust the cell references as per your actual data layout.

By using this formula, you won't need to manually replace the values for each column, making it more efficient and flexible.

I hope this helps

Kind Regards. Ajibola

Answer 5

Thankyou all for your responses, I'll experiment.

Richard.