Help with using formulas to extract specific data and displaying it in a specific format

krono

New Member
Joined
Jan 13, 2017
Messages
2
Hi everyone,

I'm currently trying to format a dataset from one worksheet to another worksheet in a specific format. I've been stuck on this problem for a few days and I was hoping to get some input or solutions for this. I have a table that looks like :


Table 1

ID Distribution
00001111 8741=100
00001234 2345=20 3321=10 1298=70
00001453 1098=50 7832=20 1123=10 5431=20

<tbody>
</tbody>


I'm trying to find a solution to pull the information from the table above into a format like this:

Table 2

1234 8741 2345 11232345
ID Distribution Name W Distribution Name X Distribution Name Y Distribution Name Z
00001111 100
0000123420
00001453 10

<tbody>
</tbody>

Basically Table 2 will have all the defined 4 digit distribution numbers displayed horizontally in row A. And based on the ID, I want to capture all the numbers to the right of the equals sign from table 1 and place them in the appropriate column.


I've tried using index match formulas combined with the right formula but it won't work if an ID has multiple distribution numbers across the row… Any advice would be much appreciated!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

krono

New Member
Joined
Jan 13, 2017
Messages
2
Bump. Does anyone know if what I'm trying to do is even possible?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,549
Members
413,996
Latest member
mabelO

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
Top