All right. I'll try to show you how the formulas work and why to use.
1. You decided the fields you want to use and put them in [F2:F15]. You could put them in any other part of another sheet.
2. Data (column [A:]) can now be cataloged with a label that allows us to recognize that it is one of the fields to be used. 1 for Label field in the first position, 2 for the second and so on. The formula is which means that if there is a field in cell A2 then look for this field in the table [F2:F15] with exact demand. The column [B:B] now has this information.
3. To organize the groups I chose the field [Membership level] <=> [label 1]. Thus, only the groups that have [Membership level] <> "" will be considered. From [B$2] (the begin) I check the frequency of the label 1 and appearance of this sort so the fields in each group with the same tag.
The formula is where 1 is the [Membership level] field position.
4. I need to build a unique key to access each of the fields of a given group. This key is formed by the group number and the name of the field. The formula is .
Now the fields are perfectly identified in the data set to [$A$2:$D$628], and its value is what is stated in column A of the line that follows the line of the field.
5 We must now explain the formula =IFERROR(INDEX($A$2:$A$628,MATCH(COLUMN(A:A)&$F2,$D$2:$D$628,0) +1,1),"(none)")
COLUMN (A: A) is 1 and is the group I want to fill. If copied to the next column gives 2 for group 2 and so on. Concatenating this value with the name of the COLUMN field (A:A)&$F2 to get [key] to access to information
Thus, simplifying the formula
IFERROR=(...,"(none)") -> If field exist then ... else "(none)"
we get
= INDEX($A$2:$A$628,MATCH(COLUMN(A:A)&$F2,$D$2:$D$628,0)+1,1)
and simplifying the writing will
= INDEX(Data,MATCH (key,data,0)+1,1)
Where +1 Is the next row to access the value of the group field identified with Key and 0 is exact match.
Eu anexei um ficheiro com a informação organizada por lista.
Best regards
Bookmarks