Como eu automatizo a classificação / alinhamento de múltiplos conjuntos de dados no Excel por valores quase que comuns?

Question

Eu gostaria de alinhar múltiplos conjuntos de dados (6+) (cada 3 colunas x 1000 linhas) por uma variável comum (coluna 'massa') onde cada conjunto terá um par de valores comuns exatamente iguais, numerosos quase correspondentes valores comuns (isto é, 635.62417 e 635.62413) e numerosos valores únicos. Mas preciso alinhar todos os 6 conjuntos de acordo com os valores quase correspondentes e manter cada uma de suas colunas adjacentes (relacionadas), incluindo valores exclusivos.

Por exemplo, com apenas três conjuntos e apenas duas colunas por conjunto, pode ser assim:

Comoprecisoalinharosconjuntosporvalorescomuns(massa)equasecomuns(desdequecorrespondamdentrode0,0005%),mantendoascolunasdeinformaçõesadjacentesdecadaconjunto(contagem)Eosvaloresnãocorrespondentes...Assim,seriasemelhante:

Como mencionei, preciso alinhar 6 conjuntos de dados de 3 colunas relacionados e, em seguida, repetir ~ 100 vezes. Portanto, quanto menos tipos / cliques manuais, melhor.

Eu tentei o Vlookup, mas ele apenas retorna a coluna comum e não suporta mais de dois conjuntos de dados (até onde sei). Também requer a criação de colunas correspondentes, o que torna difícil o alinhamento em 6 (3 colunas) conjuntos.

Eu também tentei criar uma coluna de rótulos de conjunto de dados (setA, setB, setC ..) para cada linha de cada conjunto e, em seguida, concatenar todos os 6 + conjuntos em um conjunto de colunas de 6000+ valor (agora 4), classificando por variável comum coluna (massa) e, em seguida, escrevendo uma função aninhada-if para pesquisar por 6 valores quase consecutivos com todos os 6 rótulos de conjuntos (a, b, c ...). No entanto, a função chegou a ser muito longa, e não poderia lidar com isso se um conjunto continha mais de um valor no intervalo quase o mesmo (0,0005%) antes do valor do 6º conjunto. Por exemplo, todos os 6 conjuntos podem ter valores quase comuns (dentro do intervalo de 0,0005%), mas um ou mais conjuntos tiveram dois valores dentro do intervalo, criando 7 ou mais valores consecutivos dentro do intervalo da lista classificada concatenada. p>

Manter os únicos e manter a sequência (ordem) da coluna de variáveis comuns é importante, mas talvez eu encontre algumas soluções quando souber que posso me alinhar.

Aqui está uma lista (csv list) mais completa / exata (primeiro 14 linhas) para brincar.

massA,countA,relA,massB,countB,relB,massC,countC,relC,massD,countD,relD,massE,countE,relE,massF,countF,relF
326.76558,663172.1,23,326.76561,863181.6,33.2,326.76554,2076507.5,33,326.76539,628073.8,32.18,326.76536,165294.3,32.73,326.76551,479780.8,31.48
327.76889,36016.5,1,327.76898,51965.1,2,327.76898,111976.8,2,327.76887,43550.3,2.23,327.76885,8935.5,1.77,327.76891,26583.1,1.74
328.76345,2002753.8,70,328.76352,2600265,100,328.76341,6305402,100,328.76329,1951801.5,100,328.76324,505034.4,100,328.76342,1459376.4,95.75
328.80699,2006.8,0,328.80427,2832.7,0.11,328.80594,21364.6,0,328.80591,1931.2,0.1,328.80597,378.7758,0.075,328.80603,773.5,0.05
328.7224,5367,0,328.72241,3207.2,0.12,328.72382,14715.6,0,328.72274,2441.8,0.13,328.72091,545.4,0.11,328.72237,1335.1,0.09
329.76683,118114,4,329.76693,159077.5,6.12,329.76682,368688.9,6,329.76664,111203.6,5.7,329.76665,27753.8,5.5,329.76683,85531.4,5.61
330.72018,4853.3,0,330.71986,2861.8,0.11,330.7215,14641.9,0,330.72029,3691.3,0.19,330.71862,577.2,0.11,330.71974,960.7,0.06
330.76132,1931960,68,330.76148,2597397.8,99.89,330.7613,6159470,98,330.7612,1948729.3,99.84,330.76119,491752.1,97.37,330.76136,1414513.4,92.81
330.80504,3261.9,0,330.80228,3143.2,0.12,330.80403,22439,0,330.80453,1986.6,0.1,330.80216,425.6,0.08,330.803345,1371.68667,0.09
331.76471,117533.1,4,331.76486,154505,5.94,331.7647,380896.9,6,331.7645,112448.4,5.76,331.76449,30269.2,5.99,331.76478,84866.9,5.57
332.75928,629556.9,22,332.75945,822702.6,31.64,332.75922,2007981.4,32,332.75916,616306.8,31.58,332.75917,157364.1,31.16,332.75937,447454.4,29.36
333.76267,35806.1,1,333.76281,45357.7,1.74,333.76243,123755.1,2,333.76256,36081.1,1.85,333.76247,8091.5,1.6,333.76269,24453.3,1.6
263.8424,74600.3,3,263.84242,95206.3,3.66,263.84234,200139.7,3,263.8422,100043.6,5.13,263.84221,27144.5,5.37,263.8423,57418,3.77
264.84988,3041.4,0,264.85007,2333.9,0.09,264.85021,7924.4,0,264.84999,7981.5,0.41,264.85002,1653,0.33,264.85026,559.3,0.04
265.8403,163586,6,265.84033,188887.3,7.26,265.84026,395184.9,6,265.84017,207591.2,10.64,265.8401,53300.3,10.55,265.84023,116756.2,7.66
266.84807,9515.3,0,266.84807,5249,0.2,266.84808,16061.4,0,266.84791,15974.4,0.82,266.84806,5303,1.05,266.84793,2493,0.16
266.84358,7025.1,0,266.84365,5586.2,0.21,266.8435,14268.8,0,266.84354,8026.3,0.41,266.84353,1134.2,0.22,266.84374,2020.3,0.13
267.83825,84599.6,3,267.83832,93845,3.61,267.83829,201742.9,3,267.83818,96077.6,4.92,267.83811,26448.1,5.24,267.83824,57441.8,3.77
268.84606,3934.6,0,268.84583,661,0.03,268.8461,2339.6,0,268.84588,7800,0.4,268.84604,926.6,0.18,268.84596,4419.87927,0.29
157.83689,16810.5,1,157.83697,29968.8,1.15,157.83692,70280.5,1,157.83686,34059.8,1.75,157.83686,14758.6,2.92,157.83689,28073.5,1.84
159.83488,39914,1,159.83494,70214.1,2.7,159.83491,151654.6,2,159.83485,66569.3,3.41,159.83482,32353.1,6.41,159.83488,56339.1,3.7
161.83287,20761,1,161.83294,31342.9,1.21,161.83289,73400.1,1,161.83287,38196.6,1.96,161.83281,16197.7,3.21,161.83288,25000.1,1.64
163.00345,220063.8,8,163.00347,237746.2,9.14,163.00289,30727.4,0,163.00342,57646.9,2.95,163.00337,22231.9,4.4,163.00338,119727.8,7.86
635.62323,12595.4,0,635.62417,25581.9,0.98,635.62436,58651.6,1,635.62413,17395.4,0.89,635.62372,4100,0.81,635.62393,11804.6,0.77
637.6217,37648.4,1,637.62228,52332.3,2.01,637.62194,134509.9,2,637.622,35374.4,1.81,637.62149,8803.7,1.74,637.62174,25582.2,1.68
638.62532,4504.4,0,638.62727,4862.1,0.19,638.62582,10196.8,0,638.62539,4313.6,0.22,638.62519,981.7,0.19,638.62614,948.8,0.06
639.61961,33439.9,1,639.62008,54038.1,2.08,639.61973,149375.4,2,639.61962,32844.5,1.68,639.6199,9213.8,1.82,639.62018,20927,1.37
640.62407,3861.6,0,640.62419,4089.7,0.16,640.62423,7218.1,0,640.62452,4605.5,0.24,640.62324,958.6,0.19,640.62433,1228.2,0.08
641.6175,14621.9,1,641.61778,24032.5,0.92,641.61728,55983.9,1,641.61733,16440.4,0.84,641.61719,3941.8,0.78,641.61691,7661.2,0.5
643.61397,757.5,0,643.61594,660.3,0.03,643.6151,16674.9,0,643.61623,702.9,0.04,643.61483,349.2,0.07,643.61553,838.252965,0.055
172.92396,17893.7,1,172.92408,40444.7,1.56,172.92399,87332.7,1,172.92395,21944.1,1.12,172.92396,9251.5,1.83,172.92399,20715.7,1.36
174.92203,21998.3,1,174.92207,42146.2,1.62,174.92201,84257.5,1,174.9219,22169.3,1.14,174.92192,8113.8,1.61,174.92197,20103.8,1.32
248.85526,10542.4,0,248.85531,6942.9,0.27,248.85513,32681.2,1,248.85514,6172.5,0.32,248.85512,1150,0.23,248.85526,2262.7,0.15
250.8532,27379.1,1,250.85336,17658.1,0.68,250.85317,82113.1,1,250.8532,16332.8,0.84,250.85326,2843.4,0.56,250.85325,10750.2,0.71
252.85109,7609.2,0,252.85126,5865.8,0.23,252.85123,39003.5,1,252.8511,7526.8,0.39,252.85116,880.6,0.17,252.85129,2579.4,0.17
200.91899,22578.7,1,200.91915,23582.9,0.91,200.91904,48033.5,1,200.91899,45159.5,2.31,200.91896,13365.7,2.65,200.91895,18057,1.18
202.91704,22374,1,202.91711,29167.1,1.12,202.91693,37637.1,1,202.91696,37763,1.93,202.91693,11590.6,2.3,202.91692,17850.4,1.17
654.62475,6218.2,0,654.62474,6263.9,0.24,654.62512,34917.9,1,654.62424,8703.6,0.45,654.62395,2130.8,0.42,654.62476,1277.4,0.08
656.62088,4013.7,0,656.62272,6325,0.24,656.62196,27173.5,0,656.62202,6672.7,0.34,656.62218,1515.7,0.3,656.62318,2887.1,0.19
292.86374,6998.5,0,292.86376,4749.7,0.18,292.86362,29654.4,0,292.86364,19746.4,1.01,292.86354,3251.2,0.64,292.86373,1184.8,0.08
290.86581,914.2,0,290.86575,1732.3,0.07,290.86556,6614.1,0,290.86553,7055.4,0.36,290.86553,976.2,0.19,290.86553,4191.264825,0.275
294.86156,1370.1,0,294.86207,1303.6,0.05,294.86127,6421.9,0,294.86132,4964.6,0.25,294.86109,577.4,0.11,294.861205,2743.37334,0.18
574.6986,6112.1,0,574.69877,5215.9,0.2,574.6982,25223.8,0,574.69846,8328.1,0.43,165.01896,305437.5,60.48,111.94696,1524096.3,100
576.6961,2803.3,0,576.69681,1871.3,0.07,576.69614,20312.2,0,165.019,747884.1,38.32,111.94696,282547.7,55.95,121.02922,1136269.3,74.55
572.70044,1272.1,0,572.70022,2960.5,0.11,572.70012,18267.1,0,116.9283,501429.7,25.69,116.92828,136035.1,26.94,116.92829,570097.6,37.41
280.82724,11677.6,0,280.82731,13248,0.51,280.82727,21424.3,0,111.94697,387397.6,19.85,96.95965,74242.5,14.7,96.95965,405330.9,26.59
282.82526,3499.7,0,282.84307,4110,0.16,282.82486,10716.8,0,121.02922,187257.4,9.59,121.02924,72262.8,14.31,95.95182,400722.9,26.29
588.69475,1473.4,0,588.69537,1128.8,0.04,588.69539,13532,0,96.95966,161074.3,8.25,95.95183,68601.8,13.58,165.01897,319093,20.94
592.69121,1036.3,0,592.69156,2098,0.08,592.6909,12939.6,0,157.12306,148161.1,7.59,89.02388,62551.8,12.39,137.02409,302347.7,19.84
590.69251,1597.3,0,590.69326,4618.1,0.18,590.6928,7383.8,0,132.9232,139855.3,7.17,108.04254,59873.6,11.86,131.91534,151858.3,9.96
137.02415,479309.5,17,268.84583,661,0.03,268.8461,2339.6,0,137.02412,124271.5,6.37,132.92318,44051.6,8.72,139.0034,149355.3,9.8
121.0293,1803139.5,63,268.84197,1134.3,0.04,268.84176,5871.5,0,89.02389,118919.7,6.09,137.0241,41236.9,8.17,132.92315,138549.3,9.09
108.0426,68503.5,2,111.94702,2107023.5,81.03,137.02345,20754.6,0,143.10743,117970.8,6.04,157.12304,40907.6,8.1,153.01903,136329.7,8.94