Scatter Plot combining Alphanumeric and Numeric Data

I don't see a chart of any kind in your pictures, so I am not sure I am understanding. What I do see in your pictures is the beginnings of a pivot table, though I am not sure. See if this is anything like what you are looking for (an intro to pivot tables in newer versions of Excel if needed: http://www.excel-easy.com/data-analy...ot-tables.html ):
(Note that your profile indicates Excel 2003, so I did this in 2002 that should be a closer match to the commands in 2003. If your profile is in error, change it so that you get better help in the future).
1) Select data table/database in 2nd picture.
2) Data->Pivot table and pivot chart report (to bring up the pivot table wizard). Work through the wizard to select the source data and destination sheet/range.
3) Drag "sight words" field into the row labels area. Drag "reading level" field into "column labels" area. Drag "reading level" field into "data items" area (because level is text, excel will assume you want a "count of each level" in this area).
4) Select the row labels area -> Main Pivot table menu -> Pivot table submenu -> Group and show detail -> Select desired values (start at 0, finish at 300, by 10).

Is that the kind of thing you are trying to do, or am I completely misunderstanding?
3)

Did you have any trouble following Excel Easy's instructions for creating a pivot table? Did you decide if that is what you are looking for?

With my limited experience with pivot tables and slicers and such, I still wonder if there is a way to accomplish what you want with those tools. Someone with a lot more experience with them would probably need to enter the discussion.

I don't yet know if I would use VBA or regular worksheet functions for this. The approach I see using looks something like this:

1) Two helper columns in the source data table that lookup row and column numbers for each record.
1a) The row number lookup can be a simple MATCH() function, if you change column B in the first picture so that it is just the upper boundary of each range: 300,289,279,.... =MATCH(Pic2!B2,Pic1!$B$4:$B$29,-1).
1b) Similar MATCH() function to get column number.
2) Conditional concatenation or something to combine all names that match up with each row and column number combination.
3) formulas or procedure to place each text string into its corresponding row,column position in the array/range.

If I did that with formulas only, I would certainly use several helper columns/ranges. I might do all or part (especially part 3) using VBA (either sub or UDF). I haven't yet thought through all the details, hoping that you would be able to fill in most of that. How much help would you need to develop this kind of algorithm/procedure?

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

I "borrowed" a user defined function from here:http://www.excelforum.com/tips-and-t...ml#post3096647

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the code into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

In the attached I split the upper and lower Sight Word boundary scores in columns B:C. Then find this array entered formula in D4 filled down and across.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

By the way. Since you have Excel 2016 you may have the TEXTJOIN function. It is my understanding not all skus have it. If you do you won't need the user defined function above and you won't need to save as a macro enabled file.