Transfer data from table 1 to other 3 table

[carduel]

Hello friends,

I need your help.

In Table 1 (B7: M10) to be sorted according to two criteria (B7: M7) and (B8: M8).

Should extract all A, B and C in descending order Ascending (A1, A2, A3, A4), (B1, B2, B3, B4), (C1, C2, C3, C4).

Other numbers rewrite depending on the criteria B8: M8.

Is this possible in Excel? Select the 2 criteria and results transferred in three separate tables.

Best regards

[JeteMc]

Assuming that the values in rows 13 and 14 will be entered manually paste the following array enter formula* in B15:

Please Login or Register  to view this content.

*After pasting the array enter formula formula into B15;
1) Simultaneously press the Ctrl, Shift and Enter keys,
2) Copy the array enter formula down to B16,
3) While B15 and B16 are still selected drag the fill handle of B16 across to M16.
Let us know if you have any questions.

[carduel]

The values ​​to be entered only in Table 1 of the B7: M10

[JohnTopley]

Are you saying the data in row 14 will not necessarily be 1 to 4 for each letter but an ascending sequence i.e could be 1,7 9 12 for A?

[JohnTopley]

Try in B14

=SMALL(IF($B$7:$M$7=B$13,$B$8:$M$8),COUNTIF($B$13:B13,B13))

Enter with Ctrl+Shift+Enter

Copy across to E14

Copy/paste to B & C tables

Other formulae unchanged

[carduel]

Enter data in the range B7:M10 of table 1. I sort data in row 7 and 8 of table 1. Sort results are automatically (no manualy) transferred to Table Results and their values (values remain the same).

[carduel]

can you sort out all the A, B and C of the B7: M7 and automatically transfer them to B13: E13 for A, B13: J13 for B and L13: O13 for C.

[JohnTopley]

As an example:

in O7 and copy down 3 lines to get unique list from B7:M7

=INDEX($B$7:$M$7,MATCH(0,INDEX(COUNTIF($O$6:$O6,$B$7:$M$7),0,0),0))

in B13

=$O$7 copy to other cells

in G13

=$O$8 copy to other cells

in L13

=$O$9 copy to other cells

[carduel]

=INDEX($B$7:$M$7;MATCH(0;INDEX(COUNTIF($O$6:$O$6;$B$7:$M$7);0;0);0)) ------- OK (A list)
=INDEX($B$7:$M$7;MATCH(0;INDEX(COUNTIF($O$6:$O$7;$B$7:$M$7);0;0);0)) ------- OK (B list)
=INDEX($B$7:$M$7;MATCH(0;INDEX(COUNTIF($O$6:$O$8;$B$7:$M$7);0;0);0)) ------- OK (C list)

Everything else ok (row 15 and 16).


There's still only one thing and that is how the correct value from the range B8: M8 added to the list of A, B or C and enter it into the table Results (row 14)?

=SMALL(IF($B$7:$M$7=B$13,$B$8:$M$8),COUNTIF($B$13:B13,B13))

This is what I need.

I must admit that I admire your knowledge of excel. Thank you very much.

--------------------------------------------------------------------------------------

If I change some A to B or A to C (or any other combination) and the numbers in the range B8: M8 may be the same as a result of getting two of the same values ​​and that's not good. eg. B1 and B1 can be the same.

eg.

B - 1 - 9 - 4
A - 1 - 7 - 6

If I change A - 1 - 7 - 6 to B - 1 - 7 - 6 than in results have two B - 1 - 9 - 4. This is wrong.

I must have B - 1 - 9 - 4 and B - 1 - 7 - 6.

Can you fix this?

[JohnTopley]

I had to resort to using VBA to transpose the data abd sort it:

Please Login or Register  to view this content.

in B12

=INDEX($Q$1:$Q$4,COLUMNS($A:A))

in B13

=INDEX($R$1:$T$4,COLUMNS($A:A),ROWS($1:1))

All other formulae are similar to the above (just changed range references where required).