Auto Data Input

[Guest]

Dear experts,

I've a data file with the format like the one below:

Date A/C No. A/C Name Type

At present, there are 4,000+ records. The problem is, whenever I receive
some clients' transaction records from sources, sometimes by A/C No. and
sometimes by A/C Name, I've to input the new transaction records into the
master file one by one. What I think is, the A/C No. and A/C Name are already
existed and fixed information, the only difference are transaction Date and
Type. What I am doing now is to search the existing client by A/C No or A/C
Name, then I insert a row, copy the previous row and paste to the inserted
row, finally I change the Date and Type. If there are over 300+ reocrds a
week, it is a bit clumsy and time consuming to do the job in my existing way.
Is there any better method or any ways to auto input the data? Please kindly
advise.

Thanks in advance.

 

[Arvi Laanemets]

Hi

The easiest will be, when you can use one of client identifiers (Client Code
or Client Name) as main one. P.e. With Client Name as identifier:
Create a sheet Clients with a table ClientName, ClientNo (Column headings in
A1:B1, and fill with data starting from row 2 - without any gaps);
Define a dynamic named range Clients (Insert>Name>Define) with source
=OFFSET(Clients!$A$1,1,,COUNTA(Clients!$A:$A)-1,2)
(Whenever you add new clients into this table later, the range Clients will
be adjusted automatically);
Designe your current table in following way:
Date, Type, ClientName, ClientNo
(It will be better to have entry and calculated columns in separate groups);
Select B2, and apply to it a data validation list {Data>Validation... with
Allow='List' and Source="Type1";"Type2";...;"TypeN"};
Select C2, and apply to it a data validation list with
Source=INDEX(Clients,,1)
Select D2, and enter the formula
=IF($C2="","",VLOOKUP($C2,Clients,2,0));
Select range B2:C2, copy it, extend the range down for as much rows as you
want your table be prepared, and apply PasteSpecial>Validation to this
range;
Select D2, and copy it down for same number of rows, as in previous step.

Now, for any new entry, you enter the date, select or enter Type (you arent
allowed to enter types not present in data validation list), select or enter
the name of client (again, when you type, all misstyped names are rejected),
and client code appears in next column.

When you can't determine, are clients identified by name or code, it will be
more cumbersome. A possible solution:
The Clients table will be ClientName, ClientNo, ClientName;
You have to define 2 named ranges on table clients, p.e. ClientsNames
(columns A:B) and ClientsCodes (B:C);
On your data sheet, you must have a table
Date, Type, ClientNameEntery, ClientCodeEntry, ClientName, ClientCode
You enter data into columns Date, type and either into ClientNameEntry or
ClientCodeEntry. Last 2 columns are calculated using VLOOKUP - when
ClientNameEntry<>"", then VLOOKUP($C2,ClientsNames,2,0), when
ClientNameEntry="" and ClientCodeEntry<>"", then
VLOOKUP($C2,ClientsCodes,2,0), otherwise "";