Data displayed of multiple sheet - Lookup ? Pivot ?

You can also take advantage of the built-in Categories functionality in Numbers, somewhat similar to a Pivot Table in Excel, and be done in under a minute with an extra column and one formula.

Let's say you have your data in Table 1 and a list of your target numbers (the customer numbers for which you want the full information) in a second table, something like this:

I've added an extra "Index" column on the right of my data. The formula in E2, filled down, is:

=IF(COUNTIF(Target::A,A2)>0,"Yes","No")

This simply puts a "Yes" in the cell if the customer number in that row is found in your second table listing the numbers you are interested in. Otherwise, it puts a "No."

You can then use that "Index" column to automatically organize your data into a group whose numbers are in the second table and the rest that you are not interested in. It's a simple menu pick, like this.

The result will look something like this.

You can quickly select and copy-paste the data in the "Yes" group into a new table if you want, though I usually try to leave my data in the same place rather than duplicate it.

Highly recommend experimenting with Categories. They're highly flexible, allowing you to slice and dice your data in various ways with a minimum of formulas and fuss.

SG

If I understand it correctly, Sheet 1 contains the numbers of customers you want to keep a record of, and Sheet 2 contains their information, mixed in with a bunch of junk entries?

If so, that’s easy. Let’s use an example. Suppose you’re the President of the US, and the NSA gives you a list of 20 phone numbers belonging to whistleblowers, but you have 99 on your staff and no other information is provided. Who do you complain about on Twitter?

First I create columns in Sheet 1 to hold the information I plan to look up.

Then I use an Index-Match formula to find the missing information, by looking up the one piece of information that is present on both sheets - phone number. I could also use LOOKUP for this but I prefer INDEX/MATCH.

And now I have my list.

Is that what you’re trying to do?

In my example I arbitrarily selected 5 rows for each phone number, but the proper way to determine that would be like this:

I then take the MAX of that column to find the maximum amount of rows used for a single number, and that is the factor by which I expand my table. So I would divide row by 13 and add a suffix going from -1 all the way to -13. Unused rows would result in an error. I would handle that like this:

So I would have 13 rows available for each number, but in most cases I would not need that many. The unused rows would be hidden.

If necessary you could get a bit fancier with this method. Suppose for example each number has hundreds of rows in Sheet 2 and you only want to copy rows relating to POs into Sheet 3. You could achieve this by adjusting your formula used to create the unique reference, using COUNTIFS instead of COUNTIF and adding an additional condition, so only the rows you want get assigned a reference number.

My solution was a rather complicated workaround. If SG’s solution will do what you need I recommend that, so play around with categories for a bit first. If that won’t work, then you have my solution as a backup.

In that case I would start by adding a helper column to Sheet 2 that looks like this:

This creates a unique reference number for each row. Next I would create Sheet 3 and set up my Sheet 1 numbers like this:

I divide the the row number by 5 and manually add the unique suffix, from -1 to -5. Then copy that and paste down so the pattern repeats for all 20 numbers. My 20 rows are now 100.

Finally I populate the other cells by looking up using that unique reference number. I would add more rows than I actually need and use filters to hide any empty rows.