Align data of a group of columns

Salem 80 · Jan 9, 2018

Good day to all,
I have the following Excel problem I would highly appreciate your kind help in solving .
I have two pairs of columns on the same sheet , every column has 360 rows, I,e the total number of columns is four.
I need to align the values in the columns to a common column. following I give a small scale example for clarification:
Column D and E are one group ( values in cells are formulas outputs, not numbers entered), column H and I are another group.
In the real example there are about 360 rows of data for each column, and there are hundreds of samples, so doing this manually will be very tedious .

Column	D	E		H	I
	Temp	Rate of Evap - A	Temp	Rate of Evap - B		Hoped-for Layout	Rate of Evap-A	Temp	Rate of Evap-B
	25	0.231	22	0.982				22	0.982
			29	0.245				23
	30	0.412						24
	42	0.623	39	0.336			0.231	25
								26
	50	0.632						27
								28
								29	0.245
							0.412	30
								31
								31
								32
								33
								34
								35
								36
								37
								39	0.336

And so on.
 The new layout only has only has one common "Temperature" column, and the values of the "Evaporation rate" of both elements A and B are aligned to that common column automatically .

Please feel free to ask for any clarifications.

Regards

Salem

Salem 80 · Jan 9, 2018

Sorry for the strange format, this is my first post .

Rick Rothstein · Jan 9, 2018

Where do you want the output to go to... the same columns where the original data is located (thus overwriting that original data) or to a new location (thus preserving your original data)? If a new location, where (what sheet, what columns)?

sgroath · Jan 9, 2018

One easy way to do this is to leverage tables within Excel. Make each of your input groups a table by highlighting the header and data (e.g., D1:E361) and selecting Table within the Insert tab. Keep the box checked stating that "My table has headers". Repeat for H1:I361. You will now have Table1 and Table 2 which you can rename for clarity if desired. I just kept those names for this example.

Next highlight the headers of your output and make that a table as well. Under Temp (the center column of the output table), enter your temperatures (22, 23, 23, etc). In the 1st cell of your Rate of Evap-A column in this output table, use the following formula:

=IFERROR(INDEX(Table1,MATCH([@Temp],Table1[Temp],0),2),"")

In the 1st cell of your Rate of Evap-B column in this output table, use the following formula:

=IFERROR(INDEX(Table2,MATCH([@Temp],Table2[Temp],0),2),"")

The formulas will automatically populate their column cells and you can format the output using the Table Tools Design tab.

Salem 80 · Jan 11, 2018

Good day,
I will need to keep the original data for other calculations, and therefore the new layout would be at a new location, the new location can be columns in a new sheet, but if it is more practical or straightforward to keep the new columns on the original sheet then that would also satisfy the new data arrangement needed to move to the second step.

Kind regards

Salem 80 · Jan 11, 2018

This reply ( shown above) was for the post by Mr. Rothstein

Salem 80 · Jan 13, 2018

Dear. Sgroath

Thank you very much, the formula worked perfectly.

Thank you

Align data of a group of columns

Salem 80

New Member

Excel Facts

Salem 80

New Member

Rick Rothstein

MrExcel MVP

sgroath

New Member

Salem 80

New Member

Salem 80

New Member

Salem 80

New Member

Similar threads

Forum statistics

Share this page

Align data of a group of columns

Salem 80

New Member

Excel Facts

Salem 80

New Member

Rick Rothstein

MrExcel MVP

sgroath

New Member

Salem 80

New Member

Salem 80

New Member

Salem 80

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock