We help IT Professionals succeed at work.

How to create id in excel 2007

Manojtanwar
Manojtanwar asked
on
305 Views
Last Modified: 2012-02-14
I attached two excel file one is rib and another is bank statement. In rib file there are four column date, cash, cheque, bc, total. In total u find that there is repetitoin of amount like rs 1962 is 14 times. and same amount 13 times in bank statement. i want to give these amount id. like i show in rib file. same process happen in bank statement after giving the id to amounts in rib and bank statements file. we have to give to id to all amount although they repeted once or more. then we have to match the total culumn of rib with total of bank statment have conidtion that the date of bank statment is equal or higher and total amount and id matched with rib total and id.

thanks
manoj
RIB.xlsx
BANK-STATEMENT.xlsx
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2012

Commented:
You can us the COUNTIF function to determine if there are multiple amounts, and with concatenation like:

=IF(COUNTIF(D:D,D2)>1,D2&"-"&COUNTIF($D$2:D2,D2),"") in the BANK file

and copy down, each row will have an ID based on AMOUNT - Occurrance

e.g.,

1962-1
1962-2
etc.

You can do this in both files, where D column is E column in the RIB file

I did that and totalled up all the duplicate rows where ID ended in -1 at the bottom.

Hope this helps.  See attached.

Dave
RIB.xlsx
BANK-STATEMENT.xlsx

Author

Commented:
dear dellmille

thanks for your reply. great job can it happen like that only id not with the corresponding amount. like in rib file :-

culumn E    ID

1962            1

6000             1

1962            2

after that i dont want total the duplicate values i have to match the rib total coulmn  amount with bank total column amount having condition that bank statement date is equal to or greather then the date of rib and and amount with id of rib file is equal to amount in the bank statement with id.

rib                                                           Bank statment                                          

Date              total      id                                      Date            total               id            

 31.12.2011    1962       1                                        05.01.2011   1962               1

 31.12.2011    1962      2                                         05.01.2011   1962               2

01.01.2011    3898                                               01.01.2011   3898        

i have to match all the amount in rib with bank statement. i use vlookup for matching but it fail when duplicate amount occur so i generate id.  hope i explain the problem.

Thanks
Manoj

Author

Commented:
dear dellmille

i done something with  id formula now my table of rib and bank statement(file attached)

now i want to match rib amounts of total column with amounts of total column of bank statement to know how many amount of rib actually credited into the bank statement. condition is that bank statement date is equal to or greather then the date of rib. To make multiple occurance amount unique we can use id and amount combination. pls put the formula in bot table to match the total column with condition of date.

thanks
manoj
bank-statement-revised.xlsx
rib-revised.xlsx
Most Valuable Expert 2012
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.