Combining data into a different format on a new tab

Hannah24

New Member
Joined
Jan 11, 2019
Messages
8
Hi,

I have some data which is not in a format which is useful for what I need it for

I do not want to manipulate the 'Raw' data in anyway, a I need to keep an audit trail. I do not wish to use a pivot table function, as I need to be able to drop new data into the 'Raw' data tab and an update to occur automatically.

=IFERROR(VALUE('Raw Data'!$D4:$L4,MATCH(INDEX('Raw Data'!$D$1:$L$1,MATCH(D$1,'Ra Data'!$D4:$L4,0))+1,'Raw Data'!$D$1:$L$1,0))),0)

I have been using the formula below to achieve the results I am after however, I am not happy with it, as it relies on the data being in the exact same order when new data is dropped into the 'Raw' data tab and this is not always the case. It also relies on the same number of data lines being present, when new data is dropped in and this is also not always the case.

I need a formula which has the same result as the above, but that matches against a column of unique codes and a row of unique codes (like an INDEX MATCH MATCH would). So that if the raw data shifts about. It will still populate the correct data against the correct unique codes in the reference column and row.

I know this may not make much sense, but I hope it makes sense to someone, who may be able to advise on a better formula to use.

TIA

Hannah
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
1
2
3
4
5
6
7
8
9
NINO
Lifestyle/Lifetime/Self Select
Lifestyle/Lifetime Name
Fund 1 Name/Code
Fund 1 Units
Fund 1 Value
Fund 2 Name/Code
Fund 2 Units
Fund 2 Value
Fund 3 Name/Code
Fund 3 Units
Fund 3 Values
TN123456A
Lifestyle
IP 1234
Apples
1834.31
5568.97
0
0
Oranges
40265.34835
95469.14
TN123456B
Lifestyle
IP 1234
0
0
0
0
Oranges
94.956
225.14
TN123456C
Lifestyle
IP 1234
0
0
0
0
Oranges
5805.48854
13764.81
TN123456D
Lifestyle
IP 1234
0
0
0
0
Oranges
17611.08873
41755.89
TN123456E
Self Select
0
0
0
0
Oranges
3221.381153
7637.9
TN123456F
Lifestyle
IP 1234
0
0
0
0
Oranges
8917.814736
21144.14
TN123456G
Lifestyle
IP 1234
0
0
0
0
Oranges
466.033
1104.96
TN123456H
Self Select
0
0
0
0
Oranges
4855.697
11512.86
TN123456I
Lifestyle
IP 1234
0
0
0
0
Oranges
8440.7797
20013.09
TN123456J
Lifestyle
IP 1234
0
0
0
0
Oranges
764.342
1812.26
TN123456K
Lifestyle
IP 1234
Apples
10269.3032
31177.61
Pears
1901.943
9726.54
0
0

<tbody>
</tbody>

Above I my 'Raw' Data.
 
Upvote 0
NINO
Lifestyle/Lifetime/Self Select
Lifestyle/Lifetime Name
Apple
Pears
Oranges
TN123456A
Lifestyle
IP 1234
1834.31
0
40265.34835
TN123456B
Lifestyle
IP 1234
0
0
94.956
TN123456C
Lifestyle
IP 1234
0
0
5805.48854
TN123456D
Lifestyle
IP 1234
0
0
1711.08873
TN123456E
Self Select
0
0
0
3221.381153
TN123456F
Lifestyle
IP 1234
0
0
8917.814736
TN123456G
Lifestyle
IP 1234
0
0
466.033
TN123456H
Self Select
0
0
0
4855.697
TN123456I
Lifestyle
IP 1234
0
0
8440.7797
TN123456J
Lifestyle
IP 1234
0
0
764.7797
TN123456K
Lifestyle
IP 1234
10269.3032
1901.943
0

<tbody>
</tbody>

I want the end result to look like the above.

Any help would be greatly appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top