Data sorting with multiple subtypes

[ZenZilla]

Hi all, here's my problem:

I am pulling information from a raw data workbook into a statistical analysis workbook. Each data entry is being sorted based on one of many criteria and because the criteria I need to use to separate the entries have multiple subtypes within each type I have run into a problem. For example: book5564 English 09. I am not interested in the subtypes, just the book5564. What I have been doing so far is placing all the matching entries into one worksheet and having separate worksheets for each type, rather than all into one as I would like to. And now you'll think why not just use if statements. Well, yes I am..but again this poses a problem if I throw them all into one worksheet as some entry names contain the names of others, for example book5 and book5564 so I cant just say =sumif('data'!A:A,*book5*) as it will return false information. I could just put the name of the book verbatim but some of the entries I am working with have multiple language subtypes spanning 20 years...working with 300,000 entries this method is infeasible. In addition to this I would like the data entry name (book5564) to be a cell reference to the book name column ie =sumif('data'!A:A,B10).

Perhaps I am missing an obvious solution to this problem but I have tried everything I could think of. Thanks for any help!

 

[ZenZilla]

I am sorting the data based on its name, and from there running analysis on it. An example name would be : Book10 E 15. Where E is a language code and 15 is a year code. So the primary descriptor is book10, and the subtypes are described by the language and year codes. For my analysis I need to group all data entries together that are of the same type using only the primary descriptor (in this case, book10). I am not interested in looking at the subtypes.

Currently what I've been doing to properly analyze the data is separate each book type into its own worksheet, ie book10 has its own worksheet for all of its entries and book1022 has its own worksheet etc etc. This however is taking a significant amount of time and I would like to be able to simplify this process. The reason this is an issue is that I am using referential formulas in my analysis that rely on the primary descriptor. I cannot just use *book10* as that reference as it will pull false information due to other book types containing book10 in their names ie book1022. I could type the full name into the formulas (book10 E 15) but that would take even longer than separating each into their own worksheet.

I see two possible solutions to this, im just not sure how to achieve them:

1) I continue to separate each book type into separate worksheets, but I write a macro that auto populates my statistical analysis worksheet with the new data every time I add a new worksheet.

2) I find a way to properly reference each book type, allowing me to leave all the raw data in one worksheet.

 

[oldbrewer]

book10

D

13

book102

E

14

book1020

F

15

book10

E

14

book102

F

15

book1020

F

15

book10

D

13

D

E

F

book102

E

14

book10

52

70

15

book1020

D

15

book102

15

42

45

book10

E

14

book1020

15

15

75

book102

D

15

book1020

F

15

book10

D

13

book102

E

14

is this near what you want ?

book1020

F

15

book10

E

14

I did not use year as year but as totals to demonstrate principle

book102

F

15

book1020

F

15

book10

D

13

book10

E

14

book1020

E

15

book10

E

14

book102

F

15

book10

F

15

<tbody>
</tbody>

 

[oldbrewer]

book	code	year	sales	helper
book10	D	15	37	book10D
book102	E	14	54	book102E
book1020	F	14	22	book1020F
book10	E	14	18	book10E
book102	F	15	66	book102F
book1020	F	15	44	book1020F
book10	D	14	37	book10D			13	14	15
book102	E	14	54	book102E		book1020D	22	0	0
book1020	D	13	22	book1020D		book1020E	0	0	22
book10	E	14	18	book10E		book1020F	0	22	154
book102	D	15	66	book102D		book102D	0	0	66
book1020	F	15	44	book1020F		book102E	0	162	0
book10	D	13	37	book10D		book102F	0	0	198
book102	E	14	54	book102E		book10D	74	37	37
book1020	F	15	22	book1020F		book10E	0	126	0
book10	E	14	18	book10E		book10F	0	0	44
book102	F	15	66	book102F
book1020	F	15	44	book1020F
book10	D	13	37	book10D
book10	E	14	54	book10E		book and code bundled together
book1020	E	15	22	book1020E
book10	E	14	18	book10E
book102	F	15	66	book102F
book10	F	15	44	book10F

<colgroup><col span="4"><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[ZenZilla]

I am only interested in conducting the analysis on the main type, the subtypes do not matter at all to me. The pictures I linked in post #5 show the structure of the raw data and the analysis. My main problem is how I can deal with the multiple subtypes when using references to conduct analysis.

 

[oldbrewer]

so is my post 12 what you want?

 

[ZenZilla]

Not really. Sorting the data itself is not my issue. Perhaps I am not explaining my problem very well. Let me try and restate my issue a different way:

Currently I am taking all my raw data and separating it into separate worksheets based on book type. I would like to keep it all together in one worksheet however. But the reason I have been separating it is that I cannot find a way to deal with the subtypes of the book types in my referential formulas. So by separating each type into its own worksheet I can just use for example =sum('Book10'!L:L) rather than having it all in one worksheet and use =sumif('Data'!K:K,*Book10*,L:L) which will return false information. I could use AND or OR arguments to nest all the subtypes but that would take far far far longer than simply putting each book type into separate worksheets.

 

[ZenZilla]

Essentially I see two ways to solve my problem: I can find a different way to reference the book types. Maybe excel has a different referencing function I am not aware of, or I can write a macro? Or I could continue to separate the book types into separate worksheets, but write a macro that auto fills my analysis worksheet every time a new data worksheet is added.

 

[ZenZilla]

I found a solution! Sort of. Using the INDIRECT formula I can kind of halfassed automate my analysis table. Much better than where I was before, but not quite what I wanted. It will work for now though, I appreciate all the help!

 

[SpillerBD]

From what I could see of your images, why not a PivotTable? Are you familiar with Pivot Tables?

 

[oldbrewer]

book5	33
book51	34
book52	35
book510	36
book515	37
book54	38
book5678	39					how many
book 33	40			book5	129	21
book44	41			book51	132
book99	42			book52	135
book5	43			book510	138
book51	44			book515	141
book52	45			book54	144
book510	46			book5678	147
book515	47
book54	48
book5678	49
book 33	50
book44	51
book99	52			this distinguishes between 5,51,510 etc
book5	53
book51	54			what is it you want other than this ?
book52	55
book510	56
book515	57
book54	58			formula to count how many book5????????
book5678	59
book 33	60			=SUMPRODUCT((LEFT(A1:A30,5)="book5")*1)
book44	61
book99	62

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>