Insert Two Columns To The Right From Col H, Add Header Value "Ledger" In Col I1

Nov 22, 2008

I need a macro that will insert 2 columns to the right from Col H, add header value "Ledger" in Col I1 and "Statement" in Col J1. I then need the macro to put the values for ledger and statement next to each other as they are both related (as per below screen shot). I have highlighted which units are related so you can see that all related entries will have Ledg followed by Stmt and if they are not related then will be inputted under their relevant headings. I then want the macro to delete rows which have blank cells.

Ledg StmtUnitsLedgerStatementLedg389,351.00389,351.00599,811.00Stmt599,811.00Ledg148,822.00148,822.00209,536.00Stmt209,536.00Ledg1,674,562.001,674,562.001,661,564.00Stmt1,661,564.00Stmt111,110.000.00111,110.00Stmt7,738.000.007,738.00Ledg39,164.0039,164.000.00Ledg197,088.00197,088.000.00Ledg195,525.00195,525.000.00Ledg8,859.008,859.008,860.00Stmt8,860.00Ledg90,000.0090,000.00138,648.00Stmt138,648.00Ledg717717.000.00Ledg11.000.00Ledg5,314,520.005,314,520.000.00Ledg2,096,835.002,096,835.002,402,199.00Stmt2,402,199.00Ledg114,797.00114,797.00193,797.00Stmt193,797.00

View 9 Replies


ADVERTISEMENT

Sales Ledger Formatting - Insert Item Description Without Cut / Paste 25k Rows?

Sep 17, 2013

I'm working from a sales ledger file whereby I want all invoice data in one row although the Item Number & Item Description are on the first row. Rows 2-10 (for example) are the actual details of the invoice: Invoice #; Date; Customer ID; Customer Name; QTY; etc.

Is there a formula or easier way than copying/pasting the "Item Description" to the first column (J in this case) without any related invoice data?

View 2 Replies View Related

Insert Header And Footer

May 14, 2014

Ive created a simple VBA code thats suppose to insert a header and footer. The header works but the footer doesn't. Does it matter that my footer is in "Quotes"? I would like for it to say Page of Page so the footer will say 1 of 4! I feel like its something simple just not sure...

Code:
With ActiveSheet.PageSetup
.CenterHeader = "HSIS Phone Bill"
.CenterFooter = "Page &[Page] of &[Pages]"

End With

View 3 Replies View Related

Insert Greek Delta Symbol Into Header Using VBA

Sep 24, 2012

I'm trying to figure out how to insert the delta symbol into a header using VBA. The symbol code is 0394. My line of code is the following but it is giving me a weird "D"-like symbol. Any thoughts on how to correct this?

Code:

ActiveWorkbook.Sheets("The Flux").Range("G2") = ChrW(394) & " Prev. Year"

View 2 Replies View Related

Insert Picture Into Excel Header Saved In A Word Doc?

May 16, 2008

I would like to insert a picture into excel header saved in a word doc however, when I select insert picture option it doesnt seem to work?

View 5 Replies View Related

Macro To Insert Formula Based On Column Header

Feb 24, 2009

I have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation.

For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?

View 9 Replies View Related

Excel 2010 :: Cell Insert In One Column To Insert In Other Columns?

Mar 23, 2012

I have a master data sheet with four columns, A, B, C and D

Column A has the primary data and B,C,D has dependent data values;

So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;

View 2 Replies View Related

Vba Working With Columns By Header Name

Jul 2, 2008

I am looking for a better solution working with columns in VBA.

Currently, I refer to columns by their letter or # in my VBA code.

For example:

Columns("AW:AW").FormulaR1C1 = "=+RC[-8]"
I have a scenario where I have to work with a dynamic data source that scrambles columns around (column is not always in the same position on the spreadsheet). Currenct...Also, there may be missing columns or new columns that need to be recognized.

What I would like to do is refer to my columns by header name.

For example (PSUEDO CODE):

If column ("MTD BUD") or ("MTD REV") do not exist Then
Goto ErrorHandler
Else
Columns("MTD REV").FormulaR1C1 = "=+RC[MTD BUD]"
End if
Would the best way to go about something like this be to create an array with column header names that I can run things against? How would this look?

(column headers are always in row 2)

View 10 Replies View Related

How To Delete Columns And Then Add Column Header To Remaining Ones

May 15, 2014

So I want to add some VBA Code that deletes specified columns and then insert Bolded Column headers to the remaining columns. The columns I want deleted are D,F,I,J,K. For simplicity purposes, the column headers would be A,B,C,D,E,F,G.

I tried using Columns("D,F,I,J,K").delete but I kept on getting 13 error.

[Code] ......

Attached File : Test VBA File.xlsx‎

View 3 Replies View Related

Export Specific Columns In Different Order Without Header To CSV

Jun 24, 2014

I want to export some columns of my worksheet to a csv-file. But i don't want to export all of them, i just need for example: Columns B, D and G and inside the csv-file they should be in an different order like G, B, D .

I already thought about using "union" to select the specific columns, but i can't get this to work.

View 2 Replies View Related

Columns Sorting Alphabetically Seperately And Each Has A Header Row

Oct 14, 2008

I have the following code that sorts each column sperately and it works, but believe this is a long way round and not the best way. I need the columns sorting alphabetically seperately and each has a header row.

View 2 Replies View Related

Possible To Sort Hoizontally With Multiple Columns Under One Header

Aug 6, 2008

In row 1 I want to have the names of servers, so we would have A C D E

Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.

Then B would have under it Start, End, Data, Time

And so on for C D ...

I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be

A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.

I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.

View 9 Replies View Related

Find Min Value In Multiple Columns And Match The Header

Jun 1, 2007

I am trying to find min value for a row (product price) within two columns (price lists) and return row one (company name) in third column ( see the attachmet). My first idea was to use “min” and “if” but a problem occur when there are text or zero or none values in the price list. I tried with conditional formatting, offset function, match-index and other methods without success

View 4 Replies View Related

Copy Columns With Specific Text In Header Row

Apr 16, 2008

The column header row for a string; if the header contains one of two possible strings then copy the entire column. I've tried the following code but it's not working.


Select Case oColHeader
Case InStr(oColHeader, "how much", 1) > 0
Selection.EntireColumn.Copy
Case InStr(oColHeader, "level of sat", 1) > 0
Selection.EntireColumn.Copy
End Select

View 9 Replies View Related

Create PivotTable With Two Columns Of Data For The Same Column Header?

Dec 24, 2012

Can I create a PivotTable with two columns of data for the same Column header?

I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.

View 2 Replies View Related

Find Matches In Multiple Columns And Return Header

Oct 29, 2012

Lets see if I can put this into words.

I am trying to find matches of a specific cell in various columns. Example:

Header 1 -------Header 2----------Header 3
-ABC123-----------abc123--------------abd123
-abd123-----------hjk321---------------hdn234
-Abc123-----------dsd123--------------sds332

If I searched for the value "abc123" I want it to return Headers 1 and 2 in a seperate column. It would not matter if the same value is in one column multiple times

So the results would show me the Column Heading for anything that reads: "abc123", "ABC123", "AbC123", "aBC123"

Is this possible?

View 2 Replies View Related

Simple Ledger

May 30, 2009

This is going to sound very simply to most here I'm sure. I have a need for a simple ledger, credit, debit, balance. I will only have maybe 12 accounts and want to keep track of hand written invoices and payments.

Here's what I would like: I return to my office, click on a tab for an account, enter the date and amount of an invoice in a debit column for that account. When payment is made, enter the amount received in the credit column. Having a running total in the balance column. A monthly statement option would be nice, but not necessary.

I have searched for templates of general ledgers and get results with budgets, projects . . . just too complex for my needs now.

View 11 Replies View Related

Insert Group Header Based On Consistent Value Within The Group?

Feb 10, 2014

I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.

View 1 Replies View Related

Excel 2007 :: Match Data In Two Columns And Return Header

Apr 20, 2012

I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.

I'm using Excel 2007

The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)

The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .

It's just six columns of data. The short list is in AY from AY2 : AY15
The long list is in BB from BB2:BB505
The column header "AL" is in AY1 and NL is in AZ1

View 2 Replies View Related

VBA / Find Column Header And Reformat (date) Values In Columns

Jun 28, 2013

I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).

Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.

Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer

[code]....

View 3 Replies View Related

Journal Ledger Balance

Nov 12, 2009

Ref attached sheet

I m facing a big and urgent problem in my worksheet..i have done my all work here but now i knew that there is a difference in this vb code..

I need your urgent favour to rectify it for me..

What i need is sheet1 is my journal jounal (data) and sheet2 is my reporting place where i set a macro button at top to update the report..

But in sheet2 column I i need an auto Balance of debit & credit..

View 11 Replies View Related

Convert Data From SAP Ledger

Jul 31, 2006

Attached some sample data I am working on Data is ledger printed from SAP R/3 to .pdf using pdf995 Stripped from .pdf to .xls using Able2Extract

Column A represents unit number
Column B represents unit name
Column C I pulled account number from Col E if number otherwise 0
Col D I concatenated Col E,F,G is text color blue, otherwise 0.
I then filled Col D down to replace zero with value above non-zero.

Problem :
There is no account number in C2. I thought about using text in D2 for example and creating a lookup table. The problem with that approach is that exact samy text appears for balance sheet account as ledger account. Salaries& Wages for example is both accnt 216103 (bal sht) and accnt 911000 (ledger activity).

View 4 Replies View Related

Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4

test.bmp‎

View 2 Replies View Related

Macro To Copy Data From All Columns Containing Header To A Single Column In Workbook

Apr 11, 2014

I am a macro newbie and I think this is beyond me.

I've been trying all morning with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.

So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in cell A4 or B4 or C4 etc. with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database.

There must be a way to do this that is easier than searching the internet all afternoon again

View 3 Replies View Related

Transfer Data From Journal To Ledger

Nov 18, 2009

how can transefer data from journal to ledgers accounts,

i can do journal and its formulas, ledger as well,

but i dont know how to connect them to avoid entring data 2 times.

View 14 Replies View Related

Create Ledger Account Based On Cashbook?

Mar 16, 2014

I want to create leger accounts In cashbook there is account head (ex. Hand Loan A/c, Salary A/c, Exam A/c and so on)

View 2 Replies View Related

Copy Ledger Account Names And Numbers To Each Row?

Apr 22, 2014

I've been working with ledgers to analyze companies financials in detail mostly using pivot tables and charts.

I usually get the data in a format that has the account name and number as a title on top and then each entry for this account under the title. In order to get my pivots working I need to copy the account name and number to each row until the next title.

Here is a picture to understand what I mean: Makrokyssäri.png

So is there a formula or a macro that could avoid doing the copying manually?

View 3 Replies View Related

Automated Posting From Journals To Ledger Accounts

Dec 28, 2008

I'm trying to set up automated posting from journal entries to ledger accounts.

What it boils down to is having XL match account numbers, from a 'Post Reference' column in a journal, with the corresponding ledger accounts (carrying the same respective account numbers) on another worksheet.

Then the transaction from the journal needs to be added to the identified ledger accounts in a sort of 'below-the-last-entry' manner.

View 5 Replies View Related

Create Ledger Account In Multiple Sheets?

Mar 13, 2014

I have CASHBOOK Worksheet and I want to create a LEDGER Account in Multiple sheet

View 2 Replies View Related

Extracting Data From General Ledger Download

Sep 20, 2006

For each account i download there are hundreds of pages of journals and entries, in the header of each page is information such as currency, the entries do not have have the CUR code (ie AUD, USD,CAD etc)

What i need to do is;
1. copy the CUR code to each entry line so that when i sort the data i have the CUR code to each entry

2. Copy the data to a "master" spreadsheet in it's unique tab if Column A begins with N and M.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved