Copying rows down into cells across until row=false ?

[jonski]

Sorry about the title, let me explain in more detail:

In row A, I have various listings. The title is always in caps, the user is
always in mixed case.

For example:

COW
SHEEP
Jim
Bob
Stu
RHINO
Dave
Ollie

So, no-one is interested in COWS but Jim, Bob and Stu like SHEEP. And what I
need to end up with is:

COW
SHEEP | Jim, Bob and Stu
RHINO | Dave and Ollie

You get the picture. It doesn't matter if the readers names go into one cell
each, I can concatenate them later.

Now, what I've worked out is that I can put a TRUE or a FALSE in the next
column along, with:
=IF(AND(LEN(A1)>1,EXACT(A1, UPPER(A1))),TRUE,FALSE)

So I've got

Code:
COW | TRUE
SHEEP | TRUE
Jim | FALSE
Bob | FALSE
Now, what I HAD planned on doing was a formula which would test if the cell
to the right of A1 said true or false, then concatenate the contents of the
cell below the contents of A1 in the most recent TRUE cell, then concatenate
that plus the next on and the next one until we get another TRUE. But of
course, the cell value changes too.

So I'm clearly out of my depth here! I just showed that to prove that I have
at least tried and thought about it,

 

[Pete_UK]

With your sample data in column A and your formula in column B, put
this formula in C1 and copy it down:

=IF(B1,COUNTIF(B$1:B1,TRUE),"")

It will give you a sequential count of the TRUEs in column B (i.e.
identify where the new columns (parents) should be).

Then you can put this formula in D1 and copy it down:

=IF(ISNA(MATCH(ROW(A1),C:C,0)),"",INDEX(A:A,MATCH(ROW(A1),C:C,0)))

This will produce your new headings for each row (parents). Then you
can put this formula in E1:

=IF(OR(D1="",INDEX($B:
$B,ROW(A1)+COLUMN(A1))),"",IF(D2<>"",IF(MATCH($D1,$A:$A,
0)=MATCH($D2,$A:$A,0)-1,"",INDEX($A:$A,MATCH(ROW(A1),$C:$C,
0)+COLUMN(A1))),INDEX($A:$A,MATCH(ROW(A1),$C:$C,0)+COLUMN(A1))))

This can be copied across to suit the number of child cells you are
likely to encounter (5 is enough for your sample data), then those
cells can be copied down as far as you need them. You will end up with
something like this:

COW TRUE 1 COW
SHEEP TRUE 2 SHEEP Jim Bob Stu
Jim FALSE RHINO Dave Ollie
Bob FALSE
Stu FALSE
RHINO TRUE 3
Dave FALSE
Ollie FALSE

Then you can fix the values in D1 across and down and then concatenate
your child cells. You can delete the first 3 columns.

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

Download and install the following addin from here -
http://www.download.com/Morefunc/3000-2077_4-10423159.html. Suppose the
data is in A1:A8. In A9, enter any capital letter, say A. In A12:A15,
enter COW, SHEEP, RHINO and A. In cell B12, array enter the following
formula (Ctrl+Shift+Enter) and copy down till cell B14

=IF(MATCH(A12,$A$1:$A$9,0)-MATCH(A13,$A$1:$A$9,0)=-1,"",MCONCAT(INDIRECT(ADDRESS(MATCH(A12,$A$1:$A$9,0)+1,1)&":"&ADDRESS(MATCH(A13,$A$1:$A$9,0)-1,1)),","))

Hope this helps.

 

[jonski]

You will end up with
something like this:

COW TRUE 1 COW
SHEEP TRUE 2 SHEEP Jim Bob Stu
Jim FALSE RHINO Dave Ollie
Bob FALSE
Stu FALSE
RHINO TRUE 3
Dave FALSE
Ollie FALSE

Then you can fix the values in D1 across and down and then concatenate
your child cells. You can delete the first 3 columns.

Looks good, but I'm getting some weirdness: For example (and I hope the
formatting comes out OK!)

SHEEP TRUE 1 SHEEP Dave Brian Paul
Dave FALSE COW Sue Helen
Brian FALSE CHICKENDave
Paul FALSE HORSE
COW TRUE 2 LIMUR Rik Bob
Sue FALSE
Helen FALSE
CHICKENTRUE 3
Dave FALSE
Roger FALSE
George FALSE
HORSE TRUE 4
Raj FALSE
Pritpal FALSE
Sanjay FALSE
LIMUR TRUE 5
Rik FALSE
Bob FALSE

So we're doing great until CHICKEN, where Roger and George get forgotten
about, then Raj, Pritpal and Sanjay (I'm going for diversity here!)
completely miss out on the HORSE, and we're back to normal for Rik and Bob's
LIMUR experience.

Also, in the bigger full version, there are lots of lines like (for example)

SHEEP TRUE 1 SHEEP Dave Brian Paul COW Rik Bob

Am I allowed to paste links to the file? I know it seems a bit cheeky but if
it helps...
http://stashbox.org/v/836846/example_row_column_file.xls

I really do appreciate the help so far.

I couldn't get Ashish Mathur's other option to work, perhaps it's because I
have Excel 2010 beta? But also, with over 2,000 rows, it appears I have to be
doing something manually. Unless I understood it wrong, that still means the
same amount of work?

 

[Roger Govier]

Hi

If you can accept a VBA solution, then this will do what you want

Sub CreateList()

Dim lr As Long, i As Long, j As Long, k As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row

k = 0: j = 2
For i = 1 To lr
If Right(ws1.Cells(i, 1), 2) = "TN" Then
k = k + 1: j = 2
ws2.Cells(k, 1) = ws1.Cells(i, 1)

Else
ws2.Cells(k, j) = ws1.Cells(i, 1)
j = j + 1
End If
Next i

End Sub

This will create your List on Sheet2 of the file

To Install
Copy code above
Alt+F11 to invoke the VB Editor
Alt+I+M to insert a New Module
Paste code into White pane that appears
Alt+F11 to return to Excel

To Use
Alt+F8
Select Macro name - CreateList
Run

 

[jonski]

Hi Roger,

Perfect! This wins - I noticed you'd spotted a pattern - all the "animals"
(or not in the demo spreadsheet!) must end in TN so I just added TN anything
that didn't end in TN, ran your script (which worked perfectly) and put the
names back to normal.

Absolutely brilliant, and thanks to everyone. I learnt a lot!