Column to Rows Question

[Dastnai]

Hi Everyone,

I am looking to move columns to one row. The one row will contain 20/40,30/50,40/70,100M. Is there a quick way to do this? I have roughly 95 columns and multiple formulas in the table. Listed below is an example of my current table:

BP#	Site	Basin	20/40 Avg LT	30/50 Avg LT	40/70 Avg LT	100M Avg LT	20/40 Replenish LT	30/50 Replenish LT	40/70 Replenish LT	100M Replenish LT	20/40 Min LT	30/50 Min LT	40/70 Min LT	100M Min LT	20/40 Demand Avg Per Day (Hist)	30/50 Demand Avg Per Day (Hist)	40/70 Demand Avg Per Day (Hist)	100M Demand Avg Per Day (Hist)
9701	CIG-Von Ormy	EAG	17	15	15	17	17	15	16	17	10	10	10	10	0	370	95	0
9704	CIG-Loving	PRM	17	15	15	17	17	17	16	17	7	7	2	2	0	227	273	31
9705	CIG-Lubbock	PRM	17	15	15	17	22	15	16	19	10	10	2	2	225	0	6	0
9708	Granite Peak-Rock Springs	NIO	17	15	15	17	17	15	15	17	10	10	10	10	0	62	7	0
9711	Equalizer-Victoria	EAG	30	30	30	30	30	30	30	30	10	10	10	10	0	0	88	346
9713	Maalt-Enid	MID	17	15	15	3	17	15	18	13	10	10	10	10	0	309	104	151
9717	Transflo-Fairmont	MAR	17	15	15	17	18	15	15	17	7	7	7	5	21	212	280	528
9720	MRIE-McKees Rocks	MAR	17	15	15	17	18	16	16	17	10	10	2	2	84	389	585	149
9727	MRIE-Hannibal	MAR	17	15	20	20	17	16	20	20	10	10	2	2	0	4	453	550

<colgroup><col><col><col><col span="16"></colgroup><tbody>
</tbody>

This is the outcome I am looking for:

BP#	Site	Basin	Grade	Max	Min	Inv
9701	CIG-Von Ormy	EAG	20/40	0	0	0
9701	CIG-Von Ormy	EAG	30/50	17889	0	7
9701	CIG-Von Ormy	EAG	40/70	14019	1095	10206
9701	CIG-Von Ormy	EAG	100M	2930	0	11163

<colgroup><col><col span="2"><col span="3"><col></colgroup><tbody>
</tbody>

9704	CIG-Loving	PRM	20/40	575	0	0
9704	CIG-Loving	PRM	30/50	6683	1213	5105
9704	CIG-Loving	PRM	40/70	13959	805	6814
9704	CIG-Loving	PRM	100M	1689	0	6

<colgroup><col><col span="2"><col span="3"><col></colgroup><tbody>
</tbody>

Thanks for the help!

 

[alansidman]

I'm looking at your data sheets and cannot determine where your results for Max, Min and Inv are in your data source. Please clarify as I have misplaced my crystal ball.

 

[Dastnai]

Hi Everyone,

I am looking to move columns to one row. The one row will contain 20/40,30/50,40/70,100M. Is there a quick way to do this? I have roughly 95 columns and multiple formulas in the table. Listed below is an example of my current table:

BP#	Site	Basin	20/40 Max	30/50 Max	40/70 Max	100M Max	20/40 INV	30/50 Inv	40/70 Inv	100M Inv	20/40 Min	30/50 Min	40/70 Min	100M Min	20/40 Demand Avg Per Day (Hist)	30/50 Demand Avg Per Day (Hist)	40/70 Demand Avg Per Day (Hist)	100M Demand Avg Per Day (Hist)
9701	CIG-Von Ormy	EAG	17	15	15	17	17	15	16	17	10	10	10	10	0	370	95	0
9704	CIG-Loving	PRM	17	15	15	17	17	17	16	17	7	7	2	2	0	227	273	31
9705	CIG-Lubbock	PRM	17	15	15	17	22	15	16	19	10	10	2	2	225	0	6	0
9708	Granite Peak-Rock Springs	NIO	17	15	15	17	17	15	15	17	10	10	10	10	0	62	7	0
9711	Equalizer-Victoria	EAG	30	30	30	30	30	30	30	30	10	10	10	10	0	0	88	346
9713	Maalt-Enid	MID	17	15	15	3	17	15	18	13	10	10	10	10	0	309	104	151
9717	Transflo-Fairmont	MAR	17	15	15	17	18	15	15	17	7	7	7	5	21	212	280	528
9720	MRIE-McKees Rocks	MAR	17	15	15	17	18	16	16	17	10	10	2	2	84	389	585	149
9727	MRIE-Hannibal	MAR	17	15	20	20	17	16	20	20	10	10	2	2	0	4	453	550

<tbody>
</tbody>

This is the outcome I am looking for:

BP#	Site	Basin	Grade	Max	Min	Inv
9701	CIG-Von Ormy	EAG	20/40	0	0	0
9701	CIG-Von Ormy	EAG	30/50	17889	0	7
9701	CIG-Von Ormy	EAG	40/70	14019	1095	10206
9701	CIG-Von Ormy	EAG	100M	2930	0	11163

<tbody>
</tbody>

9704	CIG-Loving	PRM	20/40	575	0	0
9704	CIG-Loving	PRM	30/50	6683	1213	5105
9704	CIG-Loving	PRM	40/70	13959	805	6814
9704	CIG-Loving	PRM	100M	1689	0	6

<tbody>
</tbody>

 

[Dastnai]

Sorry, I was just using the outcome desired to represent what I am looking for. Not the actual names. Essentially, I want 20/40, 30/50, 40/70, 100M under one row and Min,Max,Inv, ect. as columns.

 

[alansidman]

Suggest you post a realistic example of what you have and the expected outcomes that come from that data source. Otherwise, how will we know if we are actually pulling the correct data columns. I understand what you want, what I need is a map that tells me which data goes where and this needs to be explicit as there is no generic formula, PQ or VBA for this to happen. It needs to be designed.

 

[Dastnai]

Current:

BP#	Site	Basin	20/40 Max	30/50 Max	40/70 Max	100M Max	20/40 Min	30/50 Min	40/70 Min	100M Min	20/40 Inv	30/50 Inv	40/70 Inv	100M Inv
9701	CIG-Von Ormy	EAG	17	15	15	17	17	15	16	17	10	10	10	10
9704	CIG-Loving	PRM	17	15	15	17	17	17	16	17	7	7	2	2
9705	CIG-Lubbock	PRM	17	15	15	17	22	15	16	19	10	10	2	2
9708	Granite Peak-Rock Springs	NIO	17	15	15	17	17	15	15	17	10	10	10	10
9711	Equalizer-Victoria	EAG	30	30	30	30	30	30	30	30	10	10	10	10
9713	Maalt-Enid	MID	17	15	15	3	17	15	18	13	10	10	10	10

<tbody>
</tbody>

Desired outcome:

BP#	Site	Basin	Grade	Max	Min	Inv	ROP
9701	CIG-Von Ormy	EAG	20/40	0	0	0	0
9701	CIG-Von Ormy	EAG	30/50	17889	0	7	0
9701	CIG-Von Ormy	EAG	40/70	14019	1095	10206	2519
9701	CIG-Von Ormy	EAG	100M	2930	0	11163	0

<tbody>
</tbody>

.
.
.
.
.
.
.
.

 

[alansidman]

You have repeated the same outcome 3 times. Let me be more specific. If you look at your out come for CIG-Von Ormy 20/40, In the Max column you have zero, but in your raw data, there is no zero. How do I get the zero. Your output bears no resemblance to your raw data. How am I to write a solution with unrealistic data. In your output you have an ROP field, yet in the raw data there is no ROP. If you are unwilling to provide a realistic example, I have no choice but to tell you I can no longer help.

It does no good to us that you have provided the same results 3 times when asked to clarify. Good bye an good luck. I am unsubscribing from this thread.

 

[Dastnai]

This isn't a formula related question. Disregard the actual numbers or just consider them as X's. It is a formatting question. Thanks though.

 

[sandy666]

something like this?

BP#	Site	Basin	Grade	Max	Min	Inv	ROP
9701​	CIG-Von Ormy	EAG	20/40 Max	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	30/50 Max	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	40/70 Max	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	100M Max	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	20/40 Min	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	30/50 Min	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	40/70 Min	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	100M Min	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	20/40 Inv	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	30/50 Inv	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	40/70 Inv	17​	10​	12​	169​
9701​	CIG-Von Ormy	EAG	100M Inv	17​	10​	12​	169​
9704​	CIG-Loving	PRM	20/40 Max	17​	2​	12​	149​
9704​	CIG-Loving	PRM	30/50 Max	17​	2​	12​	149​
9704​	CIG-Loving	PRM	40/70 Max	17​	2​	12​	149​
9704​	CIG-Loving	PRM	100M Max	17​	2​	12​	149​
9704​	CIG-Loving	PRM	20/40 Min	17​	2​	12​	149​
9704​	CIG-Loving	PRM	30/50 Min	17​	2​	12​	149​
9704​	CIG-Loving	PRM	40/70 Min	17​	2​	12​	149​
9704​	CIG-Loving	PRM	100M Min	17​	2​	12​	149​
9704​	CIG-Loving	PRM	20/40 Inv	17​	2​	12​	149​
9704​	CIG-Loving	PRM	30/50 Inv	17​	2​	12​	149​
9704​	CIG-Loving	PRM	40/70 Inv	17​	2​	12​	149​
9704​	CIG-Loving	PRM	100M Inv	17​	2​	12​	149​
9705​	CIG-Lubbock	PRM	20/40 Max	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	30/50 Max	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	40/70 Max	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	100M Max	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	20/40 Min	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	30/50 Min	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	40/70 Min	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	100M Min	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	20/40 Inv	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	30/50 Inv	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	40/70 Inv	22​	2​	12​	160​
9705​	CIG-Lubbock	PRM	100M Inv	22​	2​	12​	160​
9708​	Granite Peak-Rock Springs	NIO	20/40 Max	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	30/50 Max	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	40/70 Max	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	100M Max	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	20/40 Min	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	30/50 Min	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	40/70 Min	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	100M Min	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	20/40 Inv	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	30/50 Inv	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	40/70 Inv	17​	10​	12​	168​
9708​	Granite Peak-Rock Springs	NIO	100M Inv	17​	10​	12​	168​
9711​	Equalizer-Victoria	EAG	20/40 Max	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	30/50 Max	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	40/70 Max	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	100M Max	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	20/40 Min	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	30/50 Min	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	40/70 Min	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	100M Min	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	20/40 Inv	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	30/50 Inv	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	40/70 Inv	30​	10​	12​	280​
9711​	Equalizer-Victoria	EAG	100M Inv	30​	10​	12​	280​
9713​	Maalt-Enid	MID	20/40 Max	18​	3​	12​	153​
9713​	Maalt-Enid	MID	30/50 Max	18​	3​	12​	153​
9713​	Maalt-Enid	MID	40/70 Max	18​	3​	12​	153​
9713​	Maalt-Enid	MID	100M Max	18​	3​	12​	153​
9713​	Maalt-Enid	MID	20/40 Min	18​	3​	12​	153​
9713​	Maalt-Enid	MID	30/50 Min	18​	3​	12​	153​
9713​	Maalt-Enid	MID	40/70 Min	18​	3​	12​	153​
9713​	Maalt-Enid	MID	100M Min	18​	3​	12​	153​
9713​	Maalt-Enid	MID	20/40 Inv	18​	3​	12​	153​
9713​	Maalt-Enid	MID	30/50 Inv	18​	3​	12​	153​
9713​	Maalt-Enid	MID	40/70 Inv	18​	3​	12​	153​
9713​	Maalt-Enid	MID	100M Inv	18​	3​	12​	153​