How to organize segments of data into a table?

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
528
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have the following segments of data, all of the same shape and dimensions, that I want to organize into a few bigger tables. There are 20 segments of data per time, which are organized in the form of 4 (vertical) X 5 (horizontal) segments. Each data point can be identified by three properties:

1) Sample name: up to 40 (only 4 shown in example)
2) Time: 1,2,3,...,15 (only 2 shown in example)
3) Replicate: 1,2,3,4 (only 3 shown in example)
4) Concentration: 1,2,3,...,12 (only 4 shown in example)

My goal is to bring all the segments of each time together, and make the sample list alphabetical as shown in the two empty lower tables of the attached example XL2BB below:

Organize.xlsx
ABCDEFGHIJKLMNOPQRS
1
2TimeConcentrationTimeConcentration
31Replicate12341Replicate1234
4Sample1134282767Sample2382356336
5Sample2263824166Sample4260677010
6Sample1265835644Sample3269632478
7Sample3123742586Sample3334891460
8Sample2171473849Sample1339827585
9Sample4350195968Sample4111442242
10
11TimeConcentrationTimeConcentration
122Replicate12342Replicate1234
13Sample3246247448Sample1274356925
14Sample4147213866Sample3371473336
15Sample1190774660Sample2290827384
16Sample2177661714Sample4214205355
17Sample1322398445Sample4318493831
18Sample3111225843Sample2120812851
19
20Time
211Concentration1Concentration2Concentration3Concentration4
22Replicate:1234123412341234
23Sample1
24Sample2
25Sample3
26Sample4
27
28Time
292Concentration1Concentration2Concentration3Concentration4
30Replicate:1234123412341234
31Sample1
32Sample2
33Sample3
34Sample4
35
Sheet1




I was trying to think of an INDEX/MATCH formula, but wasn't sure how to make it search for all 4 properties at once in discontinuous ranges. I tried to break the ranges, but INDEX didn't like it.

I would appreciate any input. Thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,215,403
Messages
6,124,710
Members
449,182
Latest member
mrlanc20

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