Using Index and Match in Numbers

Hi dm,

I have used an Automator sequence to download the data from the weblink and then I open it in Numbers and the manually split it into columns

I followed your link, and selected and copied just a few rows to test.

Clicking on a blank part of the canvas in Numbers 5.2 and pasting, I see what you see.

Numbers 5.2 recognises semicolons (;) as column separators without the need for Automator or manually separating into columns. But that is by the way. In Numbers, a new table!

Ahaa! Your information provider is giving you a "presentation" and not a workable database!

We can fix that with a little (tedious) editing.

Click on any cell in Column A, hold down the option key and press the left arrow key on the keyboard to insert a new column to the left.

Triple click on that redundant cell B3 to select all the text and Menu > Edit > Cut (command x)

Triple click on the table name to select all the text and Menu > Edit > Paste (command v).

That removes one unnecessary row (Row 3).

Type "Fund" without the quotes into cell A1.

Now cut each major heading (actually the Fund) in Scheme Code column B and paste into the relevant rows of column A

Yes, tedious, but blame your information provider 👿.

Now sort the table (I suggest sort by column A) to bring all the blank rows together so that you can delete them all at once. Sorting by Date won't work because they are currently Text values and won't sort correctly.

Sorry for the long explanation, but that should turn your table from a "presentation" into a workable, searchable data set.

Regards,

Ian.

Hi dm,

Quick question. Have you created the table NAV DATA yourself, and added the blank rows, or is that how the data arrives from your information supplier?

The reason I ask is that there are much simpler and more flexible ways of arranging the data. For example, this would be more powerful: making it easier to fill formulas down, sort the table, and create subtotals.

I shortened your descriptions to save my typing.

Regards,

Ian.

Here's a way that's not fully automatic, but not too tedious either. You will notice a slow response from Numbers on some of the steps because your data table is so large.

1. Copy-paste the script below into Script Editor (in Applications > Utilities).

2. Make sure Script Editor.app is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

3. Click the triangle 'run' button in Script Editor. This will place the data on the system clipboard.

4. In Numbers click cell A1 of your NAV DATA table, and command-v to paste.

Click 'Adjust Settings' when this pops up in the upper right:

5. Deselect , and select ; so the 'Separate Values Using' looks like this:

Your NAV DATA table should now look like this:

6. Then in your Table 1 you can do something like this (following Ian's instructions on INDEX MATCH).

The formula In B2, filled right and down:

=INDEX(NAV DATA::D,MATCH($A2,NAV DATA::$B,0))

You may find it easier to set the formula up if you have both tables on the same sheet as in my example. Also, this works when the region is set to United States and language to English. If you're in another region and the table 'Adjust Settings' isn't doing quite the right thing, then try temporarily setting the region and language for that Numbers document at File > Advanced > Language & Region in your menu.

SG

The script to copy-paste into Script Editor is this:

set theURL to "https://www.amfiindia.com/spages/NAVAll.txt?t=06102018050527"

set theSource to do shell script "curl -s " & quoted form of theURL

set the clipboard totheSource

return theSource

Hi dm,

Something to get you started while I and other users study your question.

My cheat sheet for INDEX and MATCH:

=INDEX(Column I want a return value from,

MATCH(My Lookup Value,

Column I want to Lookup against,

Enter “0″ ))

- See more at: http://www.randomwok.com/excel/how-to-use-index-match/#sthash.L2WFkFjw.dpuf

Written for Excel, but it works in Numbers.

Acknowledgement to SGIII for that link.

Regards,

Ian.

Edit: in Table 1 on Sheet 2, put ISIN, FUND etc into Row 1 (a Header Row with magical properties 😉).

Ian.