Code to enter data in reverse in Excel 1

[smalty]

I am enetering results of a competition in an Excel spreadsheet. There are 50 competitors who are listed in a matrix. The result is given to me in the form of Competitor 02 W Competitor 47 L on date 18/09/09.
I use the following code to enter the data via pop up boxes..
Sub EnterResult()
Cells.Replace What:=InputBox("ENTER 1ST PAIR OF BOWLERS eg 0247"), Replacement:=InputBox("ENTER FIRST BOWLER'S RESULT AND DATE eg W1108"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=InputBox("ENTER PAIR OF BOWLERS IN REVERSE eg 4702"), Replacement:=InputBox("ENTER FIRST BOWLER'S RESULT AND DATE eg L1108"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

The matrix consists of 'co-ordinates' at the intersections already filled in.
So what the code does is finds the string 0247 in the matrix and replaces it with W 18/09, then finds the string 4702 in the matrix and replaces it with L 18/09. What I would like is that once I have done the first bit ie entered the result for 0247 as W 18/09 to have the second result ie for 4702 ....L 18/09, entered automatically without any further input from myself.

This is perhaps a very 'dirty' way of performing these actions but with my limited knowledge of code was the best solution I was able to come up with.

So any help with my suggested requirement or tackling the problem in a completely different way would be very much appreciated.

Regards
Smalty

 

[SkipVought]

Hi,

Put your Win & Loss data in a table lile...
[tt]
W L DT
2 3 18/09
1 2 20/09
3 2 21/09
[/tt]
Then, using named ranges...
[tt]
=IF(INDEX(L,MATCH($A2,W,0),1)=B$1,"W "&INDEX(DT,MATCH($A2,W,0),1),"")
[/tt]
and the result, as the table is filled in...
[tt]
1 2 3
1 L 20/09 W 20/09
2 L 18/09 W 18/09
3 W 21/09 L 21/09
[/tt]
If you use the Data > List > Create List... feature, Excel will adjust the Named range each time you add a new row of data to the win/loss/date table.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Please disregard my solution. It is in error.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[smalty]

Skip,
Must admit I was struggling with the formula although initially I thought it was a neat solution.....had it worked of course........but thank you for the effort.
Regards
Smalty

 

[SkipVought]

I think that this will work.

1. the data entry table, using named ranges and the List feature
[tt]
WL DT
0203 18/09
0102 19/09
[/tt]
2. the formula in the matrix...
[tt]
=IF($A2=B$1,"",IF(ISNA(MATCH($A2&B$1,WL,0)),IF(ISNA(MATCH(B$1&$A2,WL,0)),"","L "&INDEX(DT,MATCH(B$1&$A2,WL,0),1)),"W "&INDEX(DT,MATCH($A2&B$1,WL,0),1)))
[/tt]
3. the result matrix
[tt]
01 02 03
01 W 19/09
02 L 19/09 W 18/09
03 L 18/09
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[smalty]

Skip,
Any chance you could do that in a sheet and email it to me.

Thanks again for your prompt perseverance

Regards
Smalty

 

[SkipVought]

ii36250

a~t

bellhelicopter

d~o~t

textron

d~o~t

com

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[smalty]

spursmalt
a t
blueyonder
dot
co
dot
uk

 

[smalty]

Skip,
Works perfectly.

Great stuff.....THANK YOU

Smalty

 

[SkipVought]

BTW, it is almost always a better strategy to drive a spreadsheet application from a TABLE, and then REPORT the results using some sort of aggregation tools like PivotTables, Lookups or Aggregation Formulas.

The aggregation in this case is not reflected in a numeric aggregation, but rather in an aggregation of data in rows/columns (matrix).

Using a matrix as a data source, poses all sorts of problems and thwarts many of Excel's powerful features. Excel is designed to use TABLES and LISTS as sources and aggregations as report outputs.

Many novice Excel users, design their application only with regard to the results. Consequently, they shoot themselves in the foot, making the maintenance of data extremely labor intensive or convoluted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[smalty]

Once again Skip I am indebted to your 'lesson' tips and knowledge

Thank you

Smalty