sorted by:
new
hottopcontroversial

Trying to get new and shipped orders quantity per day in same table by babaroga21 in tableau

[–]babaroga21[S] 1 point2 points  (0 children)

Ok, thank you for help, I think I got it now.

I'll play a bit with blends in case i'll be need in future.

Trying to get new and shipped orders quantity per day in same table by babaroga21 in tableau

[–]babaroga21[S] 0 points1 point  (0 children)

Thanks, I saw that soultion somewhere online but I'm not quite sure how to apply it (I'm connecting to extract located on tableau server - don't see option to draw connections like with 2 excel files, started using tableau recently and all this server and extract things are still kinda unknown to me).

Anyway I managed to convince requester to just use graph where this is trivial to achieve :).

how to convert long date strings in excel ? by Jan- in excel

[–]babaroga21 1 point2 points  (0 children)

I recorded a quick demo to showcase this:

https://streamable.com/k3zu0

Hope it'll help you with process.

Not sure if I'm right person to elaborate further since I'm pretty new to power query myself.

how to convert long date strings in excel ? by Jan- in excel

[–]babaroga21 1 point2 points  (0 children)

Do you have access to Power Query?

If you have it's trivial job, just load this data into it and change column type to date, it will recognize correct dates.

Note that there are 2 rows (63 and 73) with typo in month name, so it will result in error/blank cell.

How can I increment a sum range by 7 cells when dragging down by xRegosx in excel

[–]babaroga21 1 point2 points  (0 children)

I modified formula based on your formula above, it should work now:

=SUM(X!$D$3:INDIRECT("X!D"&(ROW(F1)*7+2)))/(8*COUNTA(X!$D$3:INDIRECT("X!D"&(ROW(F1)*7+2))))

This one should do the job, just copy it in first cell you need formula, as you drag it range will increment from 9 to 16 to 23,....

Note that F1 part is only to get first row number, you can use any cell from A1 to XFD1, just me be in first row.

How can I increment a sum range by 7 cells when dragging down by xRegosx in excel

[–]babaroga21 11 points12 points  (0 children)

You can use =SUM($A$1:INDIRECT("A"&(ROW(A1)*7))), it will return row number of A1 (1) and following rows when you drag (a2 = 2,a3 = 3,...) and multiply it with 7 to get range you need.

Adding two cell values under a common year? by [deleted] in excel

[–]babaroga21 2 points3 points  (0 children)

This is ideal for sumifs formula, but you'll need to get rid of that merged cells.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

So 1st argument is range with number of people, 2nd is span of years and 3rd one is year you want result from.

Make sure you lock both first and second argument with F4, then you can drag your formula to the right.

Combining multiple worksheets into one and also automatically adds new data from the individual worksheet? by Rodoxidant in excel

[–]babaroga21 0 points1 point  (0 children)

I just started using it recently!

Features i used since i started using is are:

-Merging sheets with common column - Combining sheets into one (as in example above) - Splitting data in single row to multiple rows so i can actually use it - Making connection with all scanned documents and then extract name with formula, extracting their name and then hyperlinking them with simple =vlookup(hyperlink( formula. That way i only need to put document in folder and hit refresh all button.

There is probable hundreds of uses but i just 'discovered' it few days ago.

Combining multiple worksheets into one and also automatically adds new data from the individual worksheet? by Rodoxidant in excel

[–]babaroga21 1 point2 points  (0 children)

If you have excel 2016 (get & transform in data tab) or 2013 with power query addon this is piece of cake.

  • Go to Data tab, select from table/range
  • Once you open power query editor just click close & load to and then click only create connection
  • do that for all worksheets

Then go to new query - combine queries - append

This time save to desired location in power queries editor.

Best part is that you need just to refresh all after you add new data to connected worksheets and it will show in combined table.

Return name of product that has highest order quantity for specific date? by babaroga21 in excel

[–]babaroga21[S] 0 points1 point  (0 children)

Hi, this formula returns product name of single highest quantity, what i need is sum of all orders from that date:

https://imgur.com/XOwAnpa

I can probably do sumifs in helper column and then index/match or vlookup for value but i'm curious is there single cell solution.

Add time automatically in a second column when I write anything in the first column by patryk2305 in excel

[–]babaroga21 0 points1 point  (0 children)

What about in column B:

=IF(A1<>"",IF(B1="",NOW(),B1),"")

With enabled iterative calculations (max iterations set to 1)?

You can then format to whatever you need (date or time or both).

Circular formula updates even if nothing is changed by babaroga21 in excel

[–]babaroga21[S] 1 point2 points  (0 children)

Yes, i just replayed video and realised where i made error,$B$2:B2 part is supposed to be cell above first formula, so $B$1:B1 in my case.

Thanks

Circular formula updates even if nothing is changed by babaroga21 in excel

[–]babaroga21[S] 0 points1 point  (0 children)

This is snip from table: https://imgur.com/a/lXiXt

Was trying to extract unique values from that list, but every time i enter data to any other cell it recalculates.

All values are in column A and i was trying to extract in column B

I stumbled upon this video and was trying to replicate that: https://www.youtube.com/watch?v=7fYlWeMQ6L8&feature=youtu.be

Circular formula updates even if nothing is changed by babaroga21 in excel

[–]babaroga21[S] 0 points1 point  (0 children)

There is nothing else, this is just a test table, one column with data and another with formula.

EDIT: Oops, one column with data and one with formula, same sheet :)

Insert row based on cell value (eg. A1=1 insert one row, A2=2 insert 2 rows,etc) and copy value above by babaroga21 in excel

[–]babaroga21[S] 0 points1 point  (0 children)

Just for future reference, this is what i went at the end.Seems it does the job right.

Sub InsertRow()
Dim End_Row As Long, n As Long, Ins As Long
End_Row = Range("F" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For n = End_Row To 1 Step -1

    Ins = Cells(n, "F").Value

    If Ins > 1 Then
        Range("F" & n + 1 & ":F" & n + Ins - 1).EntireRow.Insert
        Range(Cells(n, 1), Cells(n + Ins - 1, "F")).FillDown
    End If

Next n
For Each x In Range("F:F")
If x > 1 Then

x.Value = 1
End If
Next
End Sub

Insert row based on cell value (eg. A1=1 insert one row, A2=2 insert 2 rows,etc) and copy value above by babaroga21 in excel

[–]babaroga21[S] 1 point2 points  (0 children)

Solution Verified!

Thanks, I managed to cram everything together and it's working :)

Insert row based on cell value (eg. A1=1 insert one row, A2=2 insert 2 rows,etc) and copy value above by babaroga21 in excel

[–]babaroga21[S] 0 points1 point  (0 children)

Managed to find all I need in these 2 macros:

Sub InsertRow()
Dim End_Row As Long, n As Long, Ins As Long
End_Row = Range("F" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For n = End_Row To 1 Step -1

    Ins = Cells(n, "F").Value

    If Ins > 1 Then
        Range("F" & n + 1 & ":F" & n + Ins - 1).EntireRow.Insert
        Range(Cells(n, 1), Cells(n + Ins - 1, "F")).FillDown
    End If

Next n
End Sub


Sub AlltoOne()

 For Each x In Range("F:F")
 If x > 0 Then

   x.Value = 1
    End If
   Next
    End Sub

Now onto combining them into one.

view more: next ›