Accountant needs Excel help, very difficult formula set up!!!!

[TDaoud]

Hey!

I am a tax accountant trying really hard to impress my manager...long story short one of our big clients give us a very un organized Excel sheet of there trial balance... and I want to find a quick way to organize it...below is an example of what the excel looks like and what I want to look like... (Bold represent columns and rows of an excel and periods are just fillers)

A .................B ............C .................D.....
1.Cash......... .............$1,234 .........$1,235
2. .........................................................
3.1000....................................................
4.Petty Cash.............. $1,456CR .......1,567CR
5...............................................................
6.1001.....................................................

We have over 4,000 accounts ... so we have couple of issues... I need to take the account number "1000" and place it in B next to cash. I also need to take any number in column C or D with a "CR" and turn that number into a negative. So it should look like this....

A...................... B..................... C ......................D
1.Cash.............. 1000.............. $1,234 .................$1,235
2. Petty Cash..... 1001............. -$1,456............... -$1,567
3...............................................................................
4...............................................................................

Make sense? Can you please help!!!

 

[TDaoud]

=offset($a$1,(row()-1)*3) doesn't work for me, it says it's missing ")"

 

[shyy]

Not upload the file that contains sensitive information but create your own sample... I agree with Smitty, uploading a doc is usually not the best way to ask for help and I usually say the same response, but since I have done something similar to this recently, I can take a look at the setup. Do what Smitty recommend and use the HTML maker in his sig.

I wouldn;t use the offset for this.

 

[pplstuff]

I wouldn;t use the offset for this.

Why not?

After the data is organized into a table, it'll be much easier to maniplute more... If everything is exaclyt three rows apart, seems like offset ->paste special values would set up the table nicely.

 

[TDaoud]

@smitty... I'll be honest, not sure how to navigate your MrExcel - Skydrive


Also I have cleaned up the excel so it is the exact spacing of 3 apart from each.

 

[shyy]

my understanding is that is pulling information, you wouldn't have the data in the same tab as the TB, too much clutter. You would separate them in different tabs. also if multiple users were using this, I highly doubt people will keep the clean format of three rows apart. But then again it depends on the format so it Offset in this case would work.

 

[TDaoud]

Tried that offset forumla, didn't seem to work though

 

[shyy]

Information is pulled from chart of account, so you can either use the name of account or account # as the lookup value.

Excel 2010
	A	B	C	D
1	Cash	1000	1234	1235
2	Petty Cash	1001	1456CR	1567CR
3
4
5
6	Cash	1000	1234	1235
7	Petty Cash	1001	-1456	-1567
Sheet1

Cell Formulas
Range	Formula
C6	=IFERROR(IF(SEARCH("CR",VLOOKUP($B6,$B$1:$D$2,COLUMN(B1),FALSE))>0,-1*VALUE(SUBSTITUTE(VLOOKUP($B6,$B$1:$D$2,COLUMN(B1),FALSE),"CR","")),""),VLOOKUP($B6,$B$1:$D$2,COLUMN(B1),FALSE))
C7	=IFERROR(IF(SEARCH("CR",VLOOKUP($B7,$B$1:$D$2,COLUMN(B2),FALSE))>0,-1*VALUE(SUBSTITUTE(VLOOKUP($B7,$B$1:$D$2,COLUMN(B2),FALSE),"CR","")),""),VLOOKUP($B7,$B$1:$D$2,COLUMN(B2),FALSE))
D6	=IFERROR(IF(SEARCH("CR",VLOOKUP($B6,$B$1:$D$2,COLUMN(C1),FALSE))>0,-1*VALUE(SUBSTITUTE(VLOOKUP($B6,$B$1:$D$2,COLUMN(C1),FALSE),"CR","")),""),VLOOKUP($B6,$B$1:$D$2,COLUMN(C1),FALSE))
D7	=IFERROR(IF(SEARCH("CR",VLOOKUP($B7,$B$1:$D$2,COLUMN(C2),FALSE))>0,-1*VALUE(SUBSTITUTE(VLOOKUP($B7,$B$1:$D$2,COLUMN(C2),FALSE),"CR","")),""),VLOOKUP($B7,$B$1:$D$2,COLUMN(C2),FALSE))
A6	=VLOOKUP(B6,CHOOSE({1,2},B1:B2,A1:A2),2,0)
A7	=VLOOKUP(B7,CHOOSE({1,2},B2:B3,A2:A3),2,0)

 

[Smitty]

@smitty... I'll be honest, not sure how to navigate your MrExcel - Skydrive

Look for the HTMLMaker20101230 folder; the HTML Maker add-in is located there. If you download it to your PC, then you can goto File-->Options-->Add-Ins and install it. After that you'll see the HTML Maker options on the Add-Ins tab in Excel. For now I'd just select the Default option, then come back here and post the HTML Code in your post (CTRL+V). Note that when the HTML Maker renders your worksheet into code there is no message, but it has been copied to the clipboard. And when you paste it into your post don't worry if it looks like gobbledegook, the board will render it as soon as you submit.

 

[DutchDiggy]

Book1
	A	B	C	D
1	Oeh!	Nice	Feature	Smitty!
2	1	2	3	4
Blad1

Can you handle macro's of VBA in your company TDaoud?

 

[TDaoud]

Doesn't seem like it is even downloading to my computer, after highlighting the HTML and click "download" there is no prompt for a download, and I don't see any trace of a download. I am pretty good with the computer, but it seems like there is a restriction on downloads.