Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Copying rows down into cells across until row=false ?
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, |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Copying rows down into cells across until row=false ?
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 On Mar 26, 10:56*pm, jonski wrote: 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, |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Copying rows down into cells across until row=false ?
Hi,
Download and install the following addin from here - http://www.download.com/Morefunc/300...-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. -- Regards, Ashish Mathur Microsoft Excel MVP "jonski" wrote in message ... 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, |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Copying rows down into cells across until row=false ?
"Pete_UK" wrote:
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...olumn_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? |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Copying rows down into cells across until row=false ?
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 -- Regards Roger Govier jonski wrote: "Pete_UK" wrote: 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...olumn_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? |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Copying rows down into cells across until row=false ?
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! "Roger Govier" wrote: 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 -- Regards Roger Govier jonski wrote: "Pete_UK" wrote: 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...olumn_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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying & Dragging Cells & Keeping Rows On all Screens | New Users to Excel | |||
Adding Rows and copying cells | New Users to Excel | |||
allow insert rows = false for only some of the worksheet | Excel Worksheet Functions | |||
Need help copying cells into rows with spaces between | Excel Discussion (Misc queries) | |||
Copying cells from varying rows | Excel Discussion (Misc queries) |