How to extract data from 2 different cells in a large Data base for 10 different group names?

[wec43wec]

I have a data base that containes 10 columns with over 5,000 rows.

The data base has aready been sorted (when I get it) by column # 1 which has the same 10 different names in column # 1.

What I am trying to do is to test data in column # 8 (i am looking for the MAX number for each of the 10 different names in column # 1). After I determine the MAX number in column # 8 for each of the 10 different names in column #1, then I want to copy the data in column # 3 and column # 4 on the row that has the MAX data which is in column # 8 and paste this data in another worksheet within the same file.

I need this for each of the 10 different names in column #1.

Is this possible in Excel without using VBA. If VBA is the only way to do this, please advise and I will start learning this function based on your response.


Desired outcome is as follows:

Name 1 from col 1 data from column # 3 data from column # 4
Name 2 from col 1 data from column # 3 data from column # 4
Name 3 fron col 1 data from column # 3 data from column # 4
Name 4 from col 1 data from column # 3 data from column # 4
Name 5 from col 1 data from column # 3 data from column # 4
Name 6 from col 1 data from column # 3 data from column # 4
Name 7 from col 1 data from column # 3 data from column # 4
Name 8 from col 1 data from column # 3 data from column # 4
Name 9 from col 1 data from column # 3 data from column # 4
Name 10 fron col 1 data from column # 3 data from column # 4

 

[SkipVought]

Hi,

Please post an example from your sheet, rows & columns. You can abbreviate it, but it must illustrate your question. Along with that, please post the expected result exactly as it should appear.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wec43wec]

Below is a brief sasmple of a data base that has over 5,000 rows but the same 10 columns. I only
show 20 rows of data but the data base goes to row 5,000.

The rows are all "grouped/sorted" by "STATE name" but different data for each of the other 9 columns. The numbber of rows may vary.

I want to test column # 8 to determine what row within each State has the highest # of Voters.

After I determine which row within each state has the highest voter count, I want the contents in column #3 and column # 4 of that row to be linked to a Report spreadsheet.

In the Sample data below, the answer / output for the first two states should be as follows:

Richmond JKL
Dukuth MNO

Note: The above desired output would be link to a Report spreadsheet. -
*** I was unable to copy the entire database showing each of the 10 columns (the data would NOT lineup properly under each column) so I only displayed below - column #3, Column #4 and column #8 which are separated by a dask.

Thanks for your help !!!!


______Col 3 -Col 4 -Col 8

______City -County -Voters
Row 1 Reston -ABC -12,345
Row 2 Burke -ABC -23,456
Row 3 Annandal -DEF -34,567
Row 4 Chantill -GHI -45,678
Row 5 Richmond -JKL -505,678
Row 6 Norfolk -MNO -23,456
Row 7 McLean -PQR -34,567
Row 8 Springfd -STU -45,678
Row 9 Herndon -VWX -56,789
Row 10 Manassas -YZ -67,900
Row 11 Atlanta -ABC -79,011
Row 12 Griffen -DEF -90,122
Row 13 StoneMtn -GHI -101,233
Row 14 Lawerenc -JKL -112,344
Row 15 Duluth -MNO -400,444
Row 16 Roswell -PQR -134,566
Row 17 Jonesbo -STU -145,677
Row 18 MorrisBn -VWX -156,788
Row 19 SandySpg -YZ -167,899
Row 20 Dunwoody -ABC -179,010
" " " " " "
Thru " " " " " "
" " " " " "
Row 4,998 " " " " " "
Row 4,999 " " " " " "
Row 5,000 " " " " " "

 

[SkipVought]

but the same 10 columns.

What does that mean? Same WHAT???

The rows are all "grouped/sorted" by "STATE name"

WHERE is the STATE name???

I want to test column # 8 to determine what row within each State has the highest # of Voters

Huh!? 1) NO column 8! 2) No State!

In the Sample data below, the answer / output for the first two states should be as follows:

Richmond JKL
Dukuth MNO

"i am looking for the MAX number for each of the 10 different names in column # 1)"
???? Every single City appears ONCE!

Your question and example make absolutely no sense!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wec43wec]

Skip – I truly apologize for the confusion on my part for not making my request clear.

If I can, please let me clarify a few items as follows:

Quote:
Wec43 - The rows are all "grouped/sorted" by "STATE name

Skip - "WHERE is the STATE name???

Correction - the first column of the database (column # 1) has the state name. I did not include / list that column because all 10 columns could not fit on my reply to this thread, so I only listed the columns that I thought were needed to create a formula for my problem not realizing the “State” name is the first condition that is needed.

What I have listed on my last reply is three columns of sample data –
column # 3 which has the “city” name,
column #4 which has the county name (I gave sample data for the county name)
column #8 which has the voter count by city, which I need to identify the city and county within each state that has the highest number of voters.


Quote:
WEC43 - I want to test column # 8 to determine what row within each State has the highest # of Voters

Skip - Huh!? 1) NO column 8! 2) No State!

Correction - (1) column 8 is displayed in my reply to the thread which I have listed as “Col 8”. (2) the first statement above should explain why I failed to show the state in my reply.


Quote:
WEC43 - In the Sample data below, the answer / output for the first two states should be as follows:

Richmond JKL
Duluth MNO

"i am looking for the MAX number for each of the 10 different names in column # 1)"

Skip - ???? Every single City appears ONCE!

Correction - I certainly hope that my neglect to include the state column (which is explained above) clarifies my request.

If I could have paste a sample of the first sheet of my data – showing all 10 columns, this would have helped but when I did a paste / preview, the data did not stay separated by coumns, so I only showed the columns that I thought were needed – but I was wrong.

Skip - Your question and example make absolutely no sense!

Wec43 - My last comment is that I know I could manual do a sort using 2 or 3 levels, but I want to automate this processing by writing a formula in the Report sheet that will give me the same answer I am looking for..

Sorry that you had to spend extra time reviewing my issues.


I added column #1 (Col 1)

______ Col 1 Col 3 Col 4 Col 8
______ -State -City -County -Voters
Row 1 -Virginia -Reston -ABC -12,345
Row 2 -Virginia -Burke -ABC -23,456
Row 3 -Virginia -Annanda -DEF -34,567
Row 4 -Virginia -Chantil -GHI -45,678
Row 5 -Virginia -Richmond -JKL -505,678
Row 6 -Virginia -Norfolk -MNO -23,456
Row 7 -Virginia -McLean -PQR -34,567
Row 8 -Virginia -Springf -STU -45,678
Row 9 -Virginia -Herndon -VWX -56,789
Row 10 -Virginia -Manass -YZ -67,900
Row 11 -Georgia -Atlanta -ABC -79,011
Row 12 -Georgia -Griffen -DEF -90,122
Row 13 -Georgia -StoneMt -GHI -101,233
Row 14 -Georgia -Laweren -JKL -112,344
Row 15 -Georgia -Duluth -MNO -400,444
Row 16 -Georgia -Roswell -PQR -134,566
Row 17 -Georgia -Jonesbo -STU -145,677
Row 18 -Georgia -Morris -VWX -156,788
Row 19 -Georgia -SandySp -YZ -167,899
Row 20 -Georgia -Dunwood -ABC -179,010
" " " " " " " "
Thru " " " " " " " "
" " " " " " " "
Row 4,998 " " " " " " " "
Row 4,999 " " " " " " " "
Row 5,000 Texas " " " " " "

 

[SkipVought]

OK, once I got the data into my sheet, i generated this PivotTable (no VBA) in about 20 seconds, using the "Top 10" feature...
[tt]
Sum of -Voters
-State -City -County Total
-Georgia -Atlanta -ABC -79011
-Virginia -Reston -ABC -12345
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

It must be that your ACTUAL values are all POSITIVE. With that assumption, the PT as described above results...
[tt]
Sum of -Voters
-State -City -County Total
-Georgia -Duluth -MNO 400444
-Virginia -Richmond -JKL 505678
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]