It’s possible to import directly from JSON to Excel. This allows us to easily filter by some JSON fields or execute Excel queries on top of our JSON data.
I have a json file with hundreds of items following this structure.
{
"_id": "63371b0027484f4f9f00587d",
"guid": "ab4059e7-6441-4593-8272-42c53e4742d2",
"isActive": true,
"balance": "$2,120.18"
}
(for this post some formulas and menu names are in spanish as my excel and computer are in spanish and excel formulas depend on this).
The formula is:
=AGREGAR(3;3;J:J)-1
The first two parameters are for the function itself. The important one is J:J which marks the column to count. What’s important here is this is not going to count filtered items in tables.
Watch out with headers! If you have headers in your table, add -1 to your formula.
(for this post some formulas and menu names are in spanish as my excel and computer are in spanish and excel formulas depend on this).
Here is how to find and mark differences in unequal, really long lists or tables in Excel. For my example, one list is a partial list from other. Some items are missing and you’ve to find which ones are.
This is the full list.