# Data transformation in Excel

#### sfdc1

##### New Member
Hi All, looking for help on converting data from one format to another -

If you want to see data in sheet is here -

Image - http://postimg.org/image/5gdyhs5gh/

Description -
1. Transpose primary data set and organize data so that options are shown as columns.
2. If there are multiple rows for same product and same option, need to sum it up and add as one row.
3. If products are not available for certain option then we need to update that cell as 'NA'
4. 'Primary set of data' may contain multiple rows, but it will have only 3 options at given point of time.
5. Above 3 scenrios are just for explanation of this problem, I want to develop one table which is dynamic
and changes are reflected as I make updates to data

Last edited:

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Drrellik

##### Well-known Member
try this in cell G6 on sheet 2 and drag it right 3 cells.

=SUMIFS(\$C\$4:\$C\$11,\$A\$4:\$A\$11,G3,\$B\$4:\$B\$11,\$F\$4)/COUNTIFS(\$A\$4:\$A\$11,G3,\$B\$4:\$B\$11,\$F\$4)&" ("&SUMIFS(\$D\$4:\$D\$11,\$A\$4:\$A\$11,G3,\$B\$4:\$B\$11,F4)&")"

see if this helps.

~DR

#### sfdc1

##### New Member
@Drrellik Thanks for your reply, I tried and here what I got -

 10 (1) 30 (0) 30 (0)

<tbody>
</tbody>

#### Drrellik

##### Well-known Member
=SUMIFS(\$C\$4:\$C\$11,\$A\$4:\$A\$11,G3,\$B\$4:\$B\$11,\$F\$4)/COUNTIFS(\$A\$4:\$A\$11,G3,\$B\$4:\$B\$11,\$F\$4)&" ("&SUMIFS(\$D\$4:\$D\$11,\$A\$4:\$A\$11,G3,\$B\$4:\$B\$11,\$F\$4)&")"

I did not lock the last F4 in the original try again.

Replies
2
Views
447
Replies
1
Views
310
Replies
0
Views
1K
Replies
9
Views
2K
Replies
0
Views
1K

1,195,712
Messages
6,011,266
Members
441,598
Latest member
chrispaulpearce

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back