List items according to an attribute

[cicco_71]

Dear Mr. and Mrs. Excels,

I'm looking for a solution for the following problem:

I have a huge list (28 000 rows) of items (practically document subchapter titles) that are connected to their parent items (main chapters) as follows:

Chapter title	Subchapter title	Staring page no.
1.1 Cooking fish	1.1.1 Bass	5
1.1 Cooking fish	1.1.2 Salmon	12
1.1 Cooking fish	1.1.3 Catfish	50
1.1 Cooking fish	1.1.4 Hake	61
1.2 Steaming fish	1.2.1 Trout	75
1.2 Steaming fish	1.2.2 Salmon	32
and so on...

I need to make a function (I guess VBA?) that takes "1.1 Cooking fish" as input (for example in cell A1) and fills the range A2:B999 with the corresponding subchapters and their page numbers.

How do I start?...

Thank you very much!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Chapter title	Subchapter title	Staring page no.			1.1 Cooking fish
2	1.1 Cooking fish	1.1.1 Bass	5			1.1.1 Bass	5
3	1.1 Cooking fish	1.1.2 Salmon	12			1.1.2 Salmon	12
4	1.1 Cooking fish	1.1.3 Catfish	50			1.1.3 Catfish	50
5	1.1 Cooking fish	1.1.4 Hake	61			1.1.4 Hake	61
6	1.2 Steaming fish	1.2.1 Trout	75
7	1.2 Steaming fish	1.2.2 Salmon	32
8
Main

Cell Formulas
Range	Formula
F2:G7	F2	=IFERROR(INDEX(B$2:B$30000,AGGREGATE(15,6,(ROW($A$2:$A$30000)-ROW($A$2)+1)/($A$2:$A$30000=$F$1),ROWS(F$2:F2))),"")

 

[cicco_71]

Hi & welcome to MrExcel.
How about

Thank you very much! Unfortunately it won't do, because I need the space down below and later in between the lines, (to insert new chapters manually) so as I see it, formulas are out of the question. I need the sheet to be filled with static text and values, that's why I'm thinking about VBA codes.

 

[Fluff]

Ok, what sheet is your data on?

 

[cicco_71]

Ok, what sheet is your data on?

It's on a different sheet that would function as a database. To just draw you the picture, I have 700 chapters, and each and every one should have their own sheet with the corresponding subchapters on it for evaluation (clearly I'm not writing a cookbook really). Running the code manually for all 700 wouldn't be a problem (I have colleagues for that ?), though it would seem useful later not to do that either...

 

[Fluff]

It's on a different sheet that would function as a database.

Yes but what is the name of the sheet?

I have 700 chapters, and each and every one should have their own sheet

Do those sheet already exist? If so are they named the same as the chapters in col A?

 

[cicco_71]

Yes but what is the name of the sheet?

It's called "ref_Database"

Do those sheet already exist? If so are they named the same as the chapters in col A?

No, not yet. They'd named after the number of the chapters, like "1.1" or "1.2" (the chapter names are too long to be used as sheet names)

 

[Fluff]

Ok, how about

Sub cicco()
   Dim Cl As Range
   Dim Ws As Worksheet
   Dim i As Long
   
   Set Ws = Sheets("ref_Database")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Left(Cl.Value, InStr(1, Cl.Value, " ") - 1)
      Next Cl
      For i = 0 To .Count - 1
         Sheets.Add.Name = .items()(i)
         Ws.Range("A1").AutoFilter 1, .keys()(i)
         Ws.AutoFilter.Range.Columns("B:C").Copy Range("A1")
      Next i
      Ws.AutoFilterMode = False
   End With
End Sub

 

[cicco_71]

Ok, how about

Sub cicco()
   Dim Cl As Range
   Dim Ws As Worksheet
   Dim i As Long
  
   Set Ws = Sheets("ref_Database")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Left(Cl.Value, InStr(1, Cl.Value, " ") - 1)
      Next Cl
      For i = 0 To .Count - 1
         Sheets.Add.Name = .items()(i)
         Ws.Range("A1").AutoFilter 1, .keys()(i)
         Ws.AutoFilter.Range.Columns("B:C").Copy Range("A1")
      Next i
      Ws.AutoFilterMode = False
   End With
End Sub

Thank you very much, that solves my basic problem!!!

 

[cicco_71]

Ok, how about

What do I change in the code, if I want the data to be placed into a template sheet? Like I wanted to make a copy of a template sheet, and paste the relevant subchapters into B11 and below? Can you help me with that?