Excel spreadsheet question to the Braintrust!

[nikita76]

Happy Father's Day everyone!

I have an Excel spreadsheet question that I'm hoping someone can help me with!

I have 2 spreadsheets, with 500 names and times on each. Some names are on both, one or the other. There are age groups, times and points associated with each name. How do I combine the 2 spreadsheets to calculate total points for each person?

Manually?

Thanks!

NRD

[Ayles]

Pivot table should do it. You will need to combine both sheets to sum by name on the pivot.

[72doug2,2S]

Pivot Table should work, or you create a third page that sums the two together.
The third page consists of all individuals, this is perhaps more than 500?

Assuming your duplicate names are spelled the same.



Sheet1 is your first sheet
Sheet2 is your second sheet
Sheet3 is the combined Sheet1 and Sheet2

On the third sheet copy all the names from sheet1 and sheet2 paste in column A. Use the same titles and sort alphabetical and delete duplicates. Then in column D add this formula and copy down to the last name.

The sumproduct will update the two spreadsheets whether they are open or closed. If you never need to use those other two you can hardcode the new spreadsheet.

=SUMPRODUCT(--(TRIM(Sheet1!$A$1:$A$600)=TRIM(Sheet3!$A3)),--(Sheet1!$A$1:$A$600<>""),Sheet1!$D$1:$D$600)+SUMPRODUCT(--(TRIM(Sheet2!$A$1:$A$600)=TRIM(Sheet3!$A3)),--(Sheet2!$A$1:$A$600<>""),Sheet2!$D$1:$D$600)

[nikita76]

Thanks for your help guys!

I need to show time, points from first race AND time and points from second race, followed by total points in the last column.

Thanks,

NRD

[id10t]

Convert to csv file and use cat, sed, cut, and echo to create a new csv file that can be loaded into a proper database as SQL statements. Then use whatever programming language you like to connect to the db, run queries, and generate reports. Since this looks like it is for club use I'd use php and put it on the web as well

[nikita76]

Thanks id10t....unfortunately, half of that makes sense to me. What would a contractors rate be for a job like this? 45-60 minutes tops for someone that knows the procedure right?

NRD

[Shaun @ Tru6]

I think a pivot table will work for you Nikita. Here's what our order entry system spits out to let me create production orders. I have Sizes per SKUs per Store. You have Times of Races per Person. Pretty fuzzy but I think you can see it.

[72doug2,2S]

Originally Posted by nikita76

Thanks for your help guys!

I need to show time, points from first race AND time and points from second race, followed by total points in the last column.

Thanks,

NRD

So one spreadsheet is the first race and one is the second?

[72doug2,2S]

Update if each sheet is a new race.
Pivot Table should work, but relabel them time 1 and points 1/ time 2 and points 2, or you create a third page that sums the two together.
The third page consists of all individuals, this is perhaps more than 500?

Assuming your duplicate names are spelled the same.



Sheet1 is your first sheet
Sheet2 is your second sheet
Sheet3 is the combined Sheet1 and Sheet2

On the third sheet copy all the names from sheet1 and sheet2 paste in column A. Use the same titles and sort alphabetical and delete duplicates. Then in columns C, D, E, and F add these formulas and copy down to the last name.

The sumproduct will update the two spreadsheets whether they are open or closed. If you never need to use those other two you can hardcode the new spreadsheet.

Cell C3 Time 1
=SUMPRODUCT(--(TRIM(Sheet1!$A$1:$A$600)=TRIM(Sheet3!$A3)),--(Sheet1!$A$1:$A$600<>""),Sheet1!$C$1:$C$600)


Cell D Points 1
=SUMPRODUCT(--(TRIM(Sheet1!$A$1:$A$600)=TRIM(Sheet3!$A3)),--(Sheet1!$A$1:$A$600<>""),Sheet1!$D$1:$D$600)

Cell E Time 2
=SUMPRODUCT(--(TRIM(Sheet2!$A$1:$A$600)=TRIM(Sheet3!$A3)),--(Sheet2!$A$1:$A$600<>""),Sheet2!$C$1:$C$600)

Cell F Points 2
SUMPRODUCT(--(TRIM(Sheet2!$A$1:$A$600)=TRIM(Sheet3!$A3)),--(Sheet2!$A$1:$A$600<>""),Sheet2!$D$1:$D$600)

G3 adds d3+f3

[nikita76]

Thanks everybody!

NRD

[kiwiokie]

You could also use the 'sum if' function using the name as the index.

[72doug2,2S]

Originally Posted by kiwiokie

You could also use the 'sum if' function using the name as the index.

But if they are two different spreadsheets you'll need sumproduct to really work.

[Don Plumley]

I would use a simple VLOOKUP - pull the missing data from Sheet 2 into Sheet 1; then paste the results into a new sheet as data (killing the formulas) to prevent subsequent fubars. Let me know if you need an example.

[72doug2,2S]

Vlookup would work only if you didn't have duplicates in the data. The Vlookup will only take the first value found.

[kiwiokie]

Originally Posted by 72doug2,2S

But if they are two different spreadsheets you'll need sumproduct to really work.

Would make more sense to copy the tabs into one spreadsheet.

[72doug2,2S]

I probably would.