30 de jan. de 2021
Solving the Dynamic / Static Data Alignment challenge using Alignment Index Numbers
A STRATEGY to address a FREQUENTLY ASKED QUESTION
If you’re reading this, you’ve encountered this challenge. Typical posted questions include these phrases: “My comments become misaligned”, or “When I add a new item, my notes shifts to the wrong rows”, or “How do I lock my hand-entered comments with data that flows in from another sheet?”
Educators, Production Managers, Club Organizers all run into it. Here are some posts from the forum.
- I have a Master tab that's referenced on other tabs, where I then hand-enter additional information in adjacent columns. When the data on the Master tab is changed, like deleting or inserting a new row, I'd like the additional information on other tabs/sheets to move to stay in synch with the info from the Master. Right now, the additional info rows don't move, even though the Master info does, so the info is no longer on the right row.
- I have Product Data several columns wide. The left column is imported from a master list of products. The columns to the right contain notes and comments related to each product that I hand enter . From time to time new products are added to this list. If I insert a new product on the master list... the rows become out of synch.
Notificação com informação.
Esta pergunta está bloqueada, e o envio de respostas foi desativado.
O conteúdo da Comunidade pode não ter sido verificado ou atualizado. Saiba mais.
Última edição: 7 de mar. de 2023
20 de mar. de 2021
A STRATEGY to address a FREQUENTLY ASKED QUESTION
“How do I lock my hand-entered comments with data that flows in from another sheet?”
KEYWORDS: Align Data, Dynamic Data, Static Data, Rows Shift, Imported data doesn't line up, My sheet is all messed up HELP!!
Lance.NYC, que escreveu a postagem original, marcou isto como uma resposta
Todas as respostas (7)
30 de jan. de 2021
ILLUSTRATING THE ISSUE
Your situation may differ in any number of ways, but there's an underlying principle being described here. Try to see past the specific setting of this illustration - a School District Manager - and understand the mechanics of the alignment strategy.
“I run a counseling program for my district. When a teacher refers a student to me for counselling, I enter the student's info on my "master" sheet. I then assign the student to the counselling staff member who will be the best fit. Each counselor has their own tab, and that tab shows only the names that counselor has been assigned. As they work the case, they enter notes next to the name, such as intervention plans or observations.
The problem occurs when I add new students to the list. Sometimes, the new student name pushes existing students down to new rows on the counselor’s tabs, but the counselor's notes don’t move, leaving the notes on the wrong rows!”
The problem occurs when I add new students to the list. Sometimes, the new student name pushes existing students down to new rows on the counselor’s tabs, but the counselor's notes don’t move, leaving the notes on the wrong rows!”
Here’s what this District Manager is describing:
In the District Managers view, entries are made each day as Student Behavior issues are reported. The DM decides which Counselor to assign each case. You can see here that Ms Kindness is assigned three of the cases.
With the DM’s sheet as the source, a formula on Ms Kindness’s tab imports her assigned students. Student Name, Class, and Reason for Referral are the columns coming via formula from the DM's sheet.
Ms K sees only 3 rows from the DM's sheet, because those are her assigned students. Note that the formula outputs the names in alphabetical order; Aaron, Carol, David.
In the COMMENTS column, Ms K hand-enters her notes alongside each name. “Aaron’s parents are Scheduled”, “Carol will drop her 8am class”.
All is well so far, but then...
On Jan 8th, the District Manager enters a new student, Barry Budinrow, and assigns Barry to Ms Kindness.
On Ms Kindness’s tab, Barry is inserted at his position in the alphabet, after Aaron, but before Carol. Carol’s name is forced down a row, but the comment about Carol’s 8am class stays in place.
This is how DYNAMIC Data becomes misaligned with hand-entered STATIC Data
Here’s what you need to know before we go forward.
There is no inherent link between dynamic data; meaning, data that is imported from another location and is likely to be re-ordered through sorting or insertion of rows, and static data; meaning, data that you hand-enter in a cell on a spreadsheet. The problem, as you’ve probably already noticed, is that dynamic data moves around, but locally entered, static data does not. If you're looking for a Sheets command to "Lock this to that", there is no such command.
In the following series of posts, I will lay out a strategy that will allow you to keep your hand entered static comments aligned with the data they refer to.
The strategy is not terribly complex, using only a few Sheets functions. But be warned, it is not intuitive. Even experienced users have to give it a good think before they internalize it. So take your time try to understand the concepts. I’m going to assume that you are at least an intermediate level Sheets user.
Continued in next post...
Última edição: 19 de out. de 2021
30 de jan. de 2021
STRATEGY OVERVIEW
Critical Concepts/Definitions:
- SOURCE SHEET This is the sheet with the underlying info about the item; the Student Names, Employee Names, Product SKU, or New Work Order. Data here may be collected manually, or may arrive by Form Response. Formulas from other sheets will access these entries.
- THE COMMENTS SHEET, or Status Sheet, or Teacher’s tab, or Staff View tab… This sheet imports data from the SOURCE, sorting and filtering as needed. Staff then adds comments related to each imported item.
- DYNAMIC DATA refers to the output of a formula. Formulas import data from the source and output it to another place on your sheet. Typically ={A2:G}, or Filter(A2:G,) or Query(A2:G,) will import the data from the source to your tab. The position of items in the output may change due to items being added, deleted, or filtered out.
- STATIC DATA refers to info you enter directly into a cell. Static data is not the output of a formula and it does not move. You enter a comment, annotation, or status update, and you expect it to stay there, right where you put it.
- The Static data is “unaware” of what’s going on in other columns. Static data never "moves".
Given that Dynamic Data is the output of a formula, we have some control over how it is output. We can harness that control to force the dynamic data to land on the right row during import.
There's a definite sense of looking through the wrong end of the telescope - you normally think of making notes alongside the data - but in this scenario, you import the data to land on the correct row, each row of data finding its place next to its corresponding notes.
To accomplish this, you’ll create a column of ALIGNMENT INDEX NUMBERS (AIN) on both the source and destination sheets. These AIN must be in place on both the source sheet and any other sheets that will refer to the data. The same exact column of AIN is used on every sheet. The AIN must be static entries. You’ll see how this works in the next steps.
To grab a set of AINs, click this link, then click USE TEMPLATE.
Continued in next post...
Última edição: 3 de nov. de 2022
30 de jan. de 2021
IMPLEMENTING THE STRATEGY
Looking back now to the addition of Barry Budinrow's name, here's what the sheets would look like with Alignment Index Numbers in place.
A column of Alignment Index Numbers is pre-populated in column A on both the District Manager's sheet, and Ms Kindness' sheet. Here's the DM sheet:
The District Manager continues to enter new student referrals in the order they arrive. Notice how new entries are made on the next row, alongside the next available AIN. Once a student is entered on an AIN row, that AIN is forever associated with this students. As you'll see, when these names are imported to the counselor's sheet, they will find their place by finding their AIN.
In the screenshot above, a couple of new students are assigned to other counselors: AIN.1060 and AIN.1070 to John Phonettic. Then, on Jan 18, Ms Kindness is assigned student Carrie Vendor who was caught smoking. Carrie’s AIN is 1080.
Looking now at Ms Kindness’ tab, a formula* in B1 does the following:
- It includes the header labels {“Student Name”, “Class”, "Reason… "};
- If Filters the DM data to only Ms Kindness assignments.
- A VLookup searches every AIN, looking for matches in the Filtered data. When VLookup finds a match, it prints the student associated with that AIN on the correct row. Note how the formula placed Carrie on the AIN.1080 row. KEY CONCEPT: The imported Dynamic Data finds its place on the correct row during import. (If you're not getting goosebumps at this point, go back and start over!)
*A detailed explanation of the formula has been added as Addendum II
Observations:
- Ms Kindness will make her comments alongside each student. Carrie is suspended from Sport for 1 week for smoking in the bathroom. This suspension is noted on the Carrie row, which is also the AIN.1080 row.
- There are gaps in the listing for Ms Kindness. This is natural, as some AIN rows were assigned to other counselors, those AIN rows show as empty on Ms K's sheet.
- The gaps are unsightly and we want to get rid of them.
Continued in next (and last) post...
Última edição: 6 de fev. de 2021
30 de jan. de 2021
USING DATA > Create a Filter to remove blanks, or for any sorting purpose as needed.
To create more user friendly, compacted view for Ms Kindness, Data > Create a Filter tool is used.
Using DATA > Create a Filter, Ms Kindness can execute a Sort command from the Filter icons in any header column. In the screenshot, she sorted by name. IMPORTANT: You must include ALL the columns in the Data > Filter, that is, the AIN, the formula output columns, and any Comment columns. Your sort must sort them all together.
Notice that Carrie’s sports restriction is on the correct row; Carrie's name, and Sport restriction are both aligned with AIN.1080. All Ms Kindness’s comments are on the correct rows for their associated student, and the imported dynamic student is aligned with their associated AIN.
Note too that when the sheet was sorted, the AINs were sorted at the same time. David, who was the first entry on District Manager's sheet, is AIN.1010. David is now alphabetically lower on the list, but he’s still AIN.1010.
Takeaways:
- Sorting must include the AIN column and ALL columns of Comments / notes / status checks etc. Data > Create a Filter is a clean way to ensure good sorting technique.
- The comments stay with their AIN
- Each row from Data outputs to the correct AIN via the formula. (It’s the Dynamic Data that moves, not the comments!)
- We’ve been looking at Ms Kindness’ tab. Every tab for every counselor (Mr Sternman, John Phonettic) would use the same exact column of AIN.
HEADS UP
Data > Create a Filter is good
Filter Views is not compatible with this AIN strategy.
HEADS UP
If your data is arriving via Form Response, you can’t park AINs on the Form Response Sheet. In those cases, create a new tab called “MIRROR”, put the AIN in column A there, and use any array reference to fetch the FR data to the Mirror. Use the mirror now as the primary data source. Ask for help on forum as needed.
HEADS UP
When new data is entered, DATA > Create a Filters will not auto update, and new entries may arrive out of the user's view. You'll need to select Sort again from your favorite Sort column to refresh. Ask for help if you want to Alert the user that there's new data lying below their view.
I’m hoping to preserve this post as a resource and starting point for users and contributors who are facing this data alignment question. As every user’s needs will no doubt vary, get as far as you can, and then create new posts when you need help.
You can do that here: https://support.google.com/docs/community?hl=en
Best,
Lance
KEYWORD: Align Data, Dynamic Data, Static Data, Rows Shift, Imported data doesn't line up, My sheet is all messed up HELP!!
Última edição: 31 de jan. de 2021
31 de jan. de 2021
ADDENDUM I
Take a copy of the Sheet used in this tutorial. Add more students, assign them to other counselors, and get a feel for this system before applying to your own sheets.
https://docs.google.com/spreadsheets/d/1Ex-Co00k2xfD5Wsz8uLufmyzVcnSRSSAfyedoT3-txU/template/preview
NOTE that the formulas must be in the Header row, usually row 1, and NOT down in the data for the Sort to work. See B1 on tab Ms.Kindness.
NOTE that AINs must be static text. I used a formula to generate the 900 or so numbers, but then copy/pasted as text to freeze them as static text. I advise you to do the same, or just use the AINs you can lift from the sample sheet.
REMEMBER to select ALL OF THE COLUMNS before creating your Data > Create a Filter. The means, the AIN column, the imported columns, and as many columns of static data as you have. This system works by keeping static entries on the rows with their AINs. Take care to preserve this relationship.
REMEMBER Data > Create a Filter is good, but Data>Filter Views is not compatible with this strategy.
Última edição: 5 de fev. de 2021
ADDENDUM II
LOOKING AT THE FORMULA
As I mentioned, this post is written for the intermediate Sheets user. I intentionally avoided burdensome details about how the formula is written, as no two sheets are alike. The goal of the post is to introduce the concept of aligning data using the AIN framework. It's up to the intermediate user to understand the strategy, and write, test and troubleshoot the formula.
Still, a quick overview of the formula in this post might be helpful as a further illustration of the mechanics. Be assured that in the end, you will need to write your formula from scratch, modeling only the functionality demonstrated here.
If you run into trouble as you set this up on your own sheet, there are a number of contributors on the forum who can help.
With that, I'll point out a few things.
CONCEPT:
The VLookup() will search for every AIN in column A, using a Filter()'d version of the District Manager's data as the Search_Range. Since only Ms Kindness' assigned rows are returned by the Filter(), only those rows will found by VLookup() and output by the formula.
BREAKDOWN
1. HEADER ROW
Since the formula must reside in the header row, you'll want to include the header labels in the formula. This is accomplished using the {curly brace array} function of Sheets.
{"Student Name", "Class", "Reason for Referral"};
Learn about: Curly Braces {ARRAYS}
2. ARRAYFORMLA()
Arrayformula() iterates through the A2:A range, using each value (each AIN) as a Vlookup() search_term.
Learn about: Arrayformula()
3. FILTER()
Let's move to the inner move segment of the formula, as formulas make the most sense when read from the inside out. The Green underlined formula segment is a FILTER() function.
The Filter() looks at the District Manager's data on tab DM.Data!A2:F, and returns only those rows where the counselor's name in column F = "Ms Kindness". If you were to put the Filter on an empty tab, this is what it would return:
Only Ms Kindness' assignments are output by the Filter() function. Notice the missing AIN numbers - they, and their rows of data, have been filtered out by Filter().
Learn about: Filter()
WORTH MENTIONING: Your application may not need Filter(). You may just use ALL the data on the source sheet, making comments on the imported data. This Filter() illustration is common, think of Job Status "OPEN", "IN PROGRESS" or "COMPLETE". But if you don't need to work with a subset of the source data, you won't be using a Filter().
4. VLOOKUP()
Moving outward now to the Purple underlined segment, we see the VLookup() function.
Learn about: VLOOKUP() and LANCE'S VLOOKUP Demo
VLookup() searches for each and every AIN in column A. Since only Ms Kindness' students are returned by the Filter(), Vlookup will find nothing for AINs assigned to other Counselors (see AIN.1030, AIN.1060, AIN.1070)
One power-user technique employed here is the use of an {array} of index value for VLookup() Rather than just returning a single cell from a single index column, we're returning index {3, 4, 5} that is, Student Name, Class, and Infraction. Those are the columns of data the formula outputs onto Ms Kindness' tab. 3, 4 and 5 refer to the Index values from the DM's sheet. The AIN column on DM is Index 1, etc.
5. IFERROR
When VLookup() does not find the desired search_term, it will usually return an error. This would fill Ms Kindness' sheet with errors when students were either unassigned or assigned to another counselor. IFERROR suppresses the message and leave the other counselors AINs blank.
YOUR SHEET WILL BE DIFFERENT. Your criteria will be different, your columns will be different. You may opt for other methods to accomplish some of these same results. When you need assistance, just post on the forum!
Best,
Lance
Última edição: 5 de fev. de 2021
20 de mar. de 2021
A STRATEGY to address a FREQUENTLY ASKED QUESTION
“How do I lock my hand-entered comments with data that flows in from another sheet?”
KEYWORDS: Align Data, Dynamic Data, Static Data, Rows Shift, Imported data doesn't line up, My sheet is all messed up HELP!!
Lance.NYC, que escreveu a postagem original, marcou isto como uma resposta