Member Avatar for jared.geli

Hi guys I need help on adding a value from 1 row to other column 1 row below.
I know it's kinda confusing so here's what I want to do:

Column_A    Column_B    Column_C
1           25%         25%
2           15%         40%
3           25%         65%
4           35%         100%

So far I don't have a clue how to do it

Edited by jared.geli
  • 3 Contributors
  • 4 Replies
  • 221 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by jared.geli

Dani AI

Generated

As described, Column_C should be the running total (cumulative sum) of the percentages in Column_B. Since the sheet is being loaded into memory and shown in a DataGridView (as and asked about), the simplest, robust approach is to compute the running total in the DataTable after the Excel data is loaded and before/after binding the grid.

Use a numeric column for the cumulative value, iterate the rows in the DataView (so any sort/order is preserved), parse Column_B values whether they are stored as percent strings ("25%") or numeric (0.25 or 25), accumulate using Decimal for accuracy, and store the running total back into the table. Example (replace the placeholder dt with your DataTable variable):

' dt = your loaded DataTable
Dim dt As DataTable = ... ' your DataTable

Dim cumCol As String = "Column_C_calc"
If Not dt.Columns.Contains(cumCol) Then
    dt.Columns.Add(cumCol, GetType(Decimal))
End If

Dim runningTotal As Decimal = 0D

For Each drv As DataRowView In dt.DefaultView
    Dim raw As Object = drv("Column_B")
    Dim value As Decimal = 0D

    If raw IsNot Nothing AndAlso Not IsDBNull(raw) Then
        Dim s As String = raw.ToString().Trim()
        If s.EndsWith("%"c) Then
            Decimal.TryParse(s.TrimEnd("%"c), value)
            value /= 100D
        Else
            Decimal.TryParse(s, value)
            ' if Excel gave "25" meaning 25%, adjust here if needed
        End If
    End If

    runningTotal += value
    drv(cumCol) = runningTotal
Next

After that, bind/refresh the grid and format the column as percent, for example DataGridView1.Columns(cumCol).DefaultCellStyle.Format = "P0". Troubleshooting notes: ensure the iteration uses the DataView (respecting sort), handle DBNull and malformed strings, choose Decimal to avoid floating errors, and if the Excel column sometimes stores 25 vs 0.25 adjust parsing accordingly. If Column_C already exists as text, create a new numeric column to avoid type conversion errors and then rename or copy values back if needed.

Recommended Answers

All 4 Replies

Member Avatar for ddanbe

We don't have a clue either.
Is it Excel? A DataGridView? From a database?
Do you already have some code? (This may be wrong code, doesn't matter)
Please let us know.

Member Avatar for jared.geli

From column_b first row its value is 25%, column_c is equals to column b. 2nd row column_b is 15%, 2nd row column_c is equals to 40% because the 25% from first row column_c is added to 15% of 2nd row column_b. Sorry I'm confused myself too. And yes it is in Excel and it will be shown from DataGridView

Edited by jared.geli
Member Avatar for ryanjayson

Are you using dataset? can you post your codes

Member Avatar for jared.geli

so far all I can do is show the tables from Excel to DataGridView and yes I'm using Dataset in my codes not the item one

 Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$] Order By APM DESC", MyConnection)
        MyConnection.Open()
        Dim adpt = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
        Dim DtSet2 = New DataSet()
        adpt.Fill(DtSet2, "Sheet1")
        DataGridView1.DataSource = DtSet2.Tables("Sheet1").DefaultView
        MyConnection.Close()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.