I need some help with arranging rows of data.... here is a
simplification of my problem

I have two seperate lists

Z Y X M
A 1 2 B 4 6
B 7 9 A 5 8
C 7 9 D 7 9
D 8 8 C 6 8

I need to merge it so it is like this

X Y X M
A 1 2 5 8
B 7 9 4 6
C 7 9 6 8
D 8 8 7 9

so that the values of A in the first row merge with the values of A on
the second row of values into one neat row...The second list has twice
as many entries as the first but should have all the same corresponding
entries that the first list does...

Assuming A = ident, B C & D = set, max 4 duplicates:

Select the data in the second set, Cut, and Paste into the first empty
row after the first set
Select all data, (the selector to the left of the column header 1 and
above the row indicator for row 1) and Data, Sort, header row, column
A

in E2 put

=IF(A2=A1,"Del","")

in F2 put

=IF($A2=$A3,B3,"")

and formula drag that to H2

in I2 put

=IF($A2<$A4,"",IF(B4<"",B4,""))

and formula drag that to K2

in L2 put

=IF($A2<$A5,"",IF(B5<"",B5,""))

and formula drag that to N2

Select E2 to N2 and bulk-formula drag that to the bottom of your data.

Select columns E to N and Copy, Paste Special = Values back over
itsself

Select all data and Sort, header over column E

Select all rows marked 'Del' and delete

Select all data and sort over column A

Delete column E

note, if there is only one possible duplicate then ignore column I to
N
If there are more than 4 duplicates just add another set of 3 columns,
row +1 and columns B C an d D per duplicate

This should produce the required result.

--

Blah Wrote:
I need some help with arranging rows of data.... here is a
simplification of my problem

I have two seperate lists

Z Y X M
A 1 2 B 4 6
B 7 9 A 5 8
C 7 9 D 7 9
D 8 8 C 6 8

I need to merge it so it is like this

X Y X M
A 1 2 5 8
B 7 9 4 6
C 7 9 6 8
D 8 8 7 9

so that the values of A in the first row merge with the values of A
on
the second row of values into one neat row...The second list has twice
as many entries as the first but should have all the same
corresponding
entries that the first list does...


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535746

Assuming A = ident, B C & D = set, max 4 duplicates:


Actually A, B, C, and D are the names which have a total of four
replicates Z, Y, X, and M split across two seperate out of order
lists. The key problem is that the second list has twice as many
entries as the first so I can't just arrange by name and them paste the
lists next to each other. I need to merge the two lists together so
that all four replicates end up next to their name on a single row and
under their respective replicate name.

do you mean twice as many rows or twice as many columns? if twice as
many rows then you have triplicates?
To see 4 headers above 3 columns can be a little confusing

note, it is also confusing if you use what are standard column headers
A B C & D as column A entries (without a heading entry), however, the
formula will still produce what you need, just delete the blank column
D when you are done.


--

Blah Wrote:
Assuming A = ident, B C & D = set, max 4 duplicates:


Actually A, B, C, and D are the names which have a total of four
replicates Z, Y, X, and M split across two seperate out of order
lists. The key problem is that the second list has twice as many
entries as the first so I can't just arrange by name and them paste
the
lists next to each other. I need to merge the two lists together so
that all four replicates end up next to their name on a single row and
under their respective replicate name.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535746

do you mean twice as many rows or twice as many columns? if twice as
many rows then you have triplicates?
To see 4 headers above 3 columns can be a little confusing

note, it is also confusing if you use what are standard column headers
A B C & D as column A entries (without a heading entry), however, the
formula will still produce what you need, just delete the blank column
D when you are done.


I mean twice as many rows ie

List 1
Batting Score Football Score
Ben 3 4


List 2
Hockey Score Skating Score
Ace 1 2
Ben 3 7

All the entries in List 1 have 4 values. Half in List one and half in
List 2. List 2 has all the entries of List 1 and more entries that are
not in List 1. I can't arrange them seperately with the sort function
because since List 2 has twice as many entries the names still won't
align with each other. I need to get the scores next to their
respective name in one list. The scores have to stay in the right
column too.

-"The scores have to stay in the right column too"-
a point not mentioned in your first post.

List 1 has Batting Score and Football Score in columns B & C

List 2 has Hockey score and Skating score in columns E and F ? with
names in column D ?
and there are twice as many Hockey & Skating scores as there are
Batting & Football ?

and you want a complete list of names in column A with Cricket in B,
Football in C, D = optional, Hockey in E, and Skating in F

is this correct?

--

Blah Wrote:
do you mean twice as many rows or twice as many columns? if twice as
many rows then you have triplicates?
To see 4 headers above 3 columns can be a little confusing

note, it is also confusing if you use what are standard column headers
A B C & D as column A entries (without a heading entry), however, the
formula will still produce what you need, just delete the blank column
D when you are done.


I mean twice as many rows ie

List 1
Batting Score Football Score
Ben 3 4


List 2
Hockey Score Skating Score
Ace 1 2
Ben 3 7

All the entries in List 1 have 4 values. Half in List one and half in
List 2. List 2 has all the entries of List 1 and more entries that are
not in List 1. I can't arrange them seperately with the sort function
because since List 2 has twice as many entries the names still won't
align with each other. I need to get the scores next to their
respective name in one list. The scores have to stay in the right
column too.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535746

If that was correct, then the attached will show the correct sequence to
easily achieve your re-organisation

As shown, insert 3 columns at E to move the X & M scores to H & I

select columns D to I and CUT, - Paste after the end of your Set 1
data.

Select All Data and sort, header row, over column A

insert at G2 … =IF(A2=A1,"Del","")

insert at H2 … =IF($A2=$A3,B2&B3,IF(B2="","",B2))
formula drag this to L2
clear cell J2

Select G2 to L2 and bulk formula drag to the end of your data

Select columns G to L and Copy, then Paste Special = values back over
themselves.

Select all data and Sort, header row, over column G

Select and delete all DEL rows

Select All data and sort over column A
Delete columns B through G

That should be your required output - see sheet Step 6

Attachment:
http://www.excelforum.com/attachment...2&d=1145962405

--

Bryan Hessey Wrote:
-"The scores have to stay in the right column too"-
a point not mentioned in your first post.

List 1 has Batting Score and Football Score in columns B & C

List 2 has Hockey score and Skating score in columns E and F ? with
names in column D ?
and there are twice as many Hockey & Skating scores as there are
Batting & Football ?

and you want a complete list of names in column A with Cricket in B,
Football in C, D = optional, Hockey in E, and Skating in F

is this correct?

--


+-------------------------------------------------------------------+
|Filename: ReOrg.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4692 |
+-------------------------------------------------------------------+

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=535746