retrieve unique data

[pi567]

I would greatly appreciate it if someone could assist me with this. I
have a file with massive amounts of data:

STATE INVOICE AMOUNT
NY 123 $100
NY 34A 25
CA 4453A1 75
NV F123 75
NV B678 45
SD 123 56
AZ 34A 100

From this file, I would like to use a formula that would pull data by

STATE to individual sheets without using macros. So, a sheet for NV
would retrieve F123 $75 and B678 $45.

What Excel function should I use? When I use vlookup, it only picks up
the first invoice. How do I go about getting all invoices for NV?

Please advise.

Thank you so much.

 

[Max]

One way ..

Try this sample construct from my archives
which delivers the required automation .. :
http://www.savefile.com/files/151988
AutoCopy_Data_To_Resp_Sheet_Non_Array_Approach.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col.

In the sample, the key col in the master sheet is the "Colours" col, which
may contain eg: Red, Yellow, Green, etc. All lines with "Red" in the key col
will be auto-copied to the sheet named: Red, and appear neatly bunched at
the top. Ditto for lines with "Yellow", "Green", etc.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the key col value. Eg we first
formulate one child sheet for "Red", dress it up nicely, then just make
copies of the "Red" sheet, and rename these as: Yellow, Green, etc

In your instance, the key col would be "State"

---

 

[Max]

Here's a revised sample customised to suit your situation:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls

In the "child" sheet,

The point formula in A2 has been slightly revised to:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),0)))
with A2 then copied across 3 cols and filled down as required

The earlier sample's formula in A2 covers only cols K to M.
The above extends it all the way to col IV.

---

 

[Don Guillett]

Would a simple SUMIF formula work?
=sumif(a2:a22,"NV",c2:c22)
or
=sumproduct((a2:a22="NV")*c2:c22)

 

[Max]

The construct notes in the revised sample for easy reference:

Here's one play which automates it using non-array formulas ..

In sheet: WS1 (the "master")

Assume data in cols A to C, data in row2 down,
with the key col = col A (State)

List the states in K1 across: NY, NV, CA, etc (can be in any order)

Put in K2: =IF($A2=K$1,ROW(),"")
Copy across as far as required, then fill down to cover the max
expected extent of data

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came
from a post by Harlan.

In a new sheet named: NY
With the same col headers pasted into A1:C1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),0)))

Copy A2 across to C2, fill down to say, C10
(copy down by the smallest possible range sufficient to cover the max
expected extent for any state. Here, I've assumed that 9 rows (rows 2
to 10) is sufficient)

Cols A to C will return only the lines for the state: NY from "WS1",
with all lines neatly bunched at the top

Now, just make a copy of the sheet: NY, rename it as the next state:
CA, and we'd get the results for the state: CA. Repeat the copy >
rename sheet process to get the rest of the state sheets (a one-time
job). Adapt to suit ..

---

 

[Earl Kiosterud]

pi,

Autofilter can pull a subset (like the rows of a given state) for you, which
you can copy/paste elsewhere. Advanced Filter can do so with fancier query
criteria, and copy the data as part of the process.

Some things to consider:

You should use this pulled data only for an individual need. Don't try to
permanently update the pulled data, or keep the data separate from the main
table for future use, or you'll lose a lot of Excel functionality later.
See "Data across multiple sheets" at
http://www.smokeylake.com/excel/excel_truths.htm for a short treatise on
this.