regrouping data

[blumfeld0]

hi. i have attached a excel file that hopefully explains clearly what i am trying to do. please let me know if you have any questions of course.thank you

-B

[blumfeld0]

I should not that I am trying to round the numbers to the nearest tens
so 855=860
864.75=860
888=890

thank you

[Bob Phillips]

[vba]

Option Explicit

Sub ProcessData()
Dim iLastRow As Long
Dim i As Long
Dim checkValue As Double
Dim iBilled As Long

With ActiveSheet

checkValue = .Range("A3").Value
.Range("E2").Value = "Billed"
.Range("F2").Value = "Sold"
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

i = 3: iBilled = 3

Do While Abs(.Cells(i, "B").Value - checkValue) >= _
Abs(.Cells(i + 1, "B").Value - checkValue)

.Range("F3").Insert Shift:=xlDown
.Range("F3").Value = .Cells(i, "B").Value
i = i + 1
Loop

.Range("F3").Insert Shift:=xlDown
.Range("F3").Value = .Cells(i, "B").Value

Do While i <= iLastRow

.Cells(iBilled, "E").Value = .Cells(i, "B").Value
iBilled = iBilled + 1
i = i + 1
Loop
End With
End Sub

[/vba]

[blumfeld0]

thank you xld.
i just tried thisand works well.
what if i have a worksheet where I have several (up to 12 sets=24 columns total) columns like column A and B
do i need to run this macro every single time.
to organize the data.
what if i had a column {A, B} {C, D} {E,F} etc where the data was orgaqnized like this. how would this macro be different? what do i do then?
thank you

[Bob Phillips]

Can you give a slightly extend example and explain how it will go further.

[blumfeld0]

Yes thank you. absolutely. please see attachment
basically, i have one worksheet with many (up to 12 sets=24 columns) of data like this that needs to be regrouped.
in the attachment I have 2 sets = 4 columns that need to be regrouped (columns {A,B} and {I,J})
so im just asking how does the macro change to incorporate the fact that many columns will need to be regrouped?

thank you

[Bob Phillips]

blumfeld,

what I was hoping to get but what your example didn't really help me with was an idea of the data. Your example shows the output which of course will not be there when you start. Perhaps I can articulate the sort of questions.

- will the groups of data be in A/B, C/D, E/F, or say A/B, D/E, G/H, or some other layout?

- do you want the results on a new sheet, to be insetted alongside, or some other?

- I assume that the number of groups is not necessarily known in advance.

[blumfeld0]

Hi xld. sorry i did not understand you.
- will the groups of data be in A/B, C/D, E/F, or say A/B, D/E, G/H, or some other layout?

they will be some other random layout.
the first group might be in {J,AC} and the next one might be in {AG, BC}
i was just going to to make it {A,B} {C,D} afterwards so i could implement your macro. but if there is a way to avoid this, that would be great. if there isn't a way then that would be fine too.

- do you want the results on a new sheet, to be insetted alongside, or some other?
hum. good question. i think i would like them to right next to the input in the same worksheet.
so if the input is in {A,B} then the output would be in columns C and D.
if the input is {A,J}, then the output should be in columns K and L.
if the input is in {BG, BK} then the results would be in columns BL and BM
this does matter too much because i could just use "=" to put them wherever i want after the macro is implemented.

- I assume that the number of groups is not necessarily known in advance.

the number of groups in not known in advance. it changes for each data set.


thank you sir!!

-B

[Bob Phillips]

Oh! Am I right in understanding that the columns are not necessarily contiguosuly paired, that is it won't be A & B but maybe A & G or B & J, interspersed with blank columns? Can we assume there will always be pairs, and if so can I remove the intervening blank coumns?

[blumfeld0]

Hi XLD how are you?
thank you for your question.

"Am I right in understanding that the columns are not necessarily contiguosuly paired, that is it won't be A & B but maybe A & G or B & J, interspersed with blank columns? Can we assume there will always be pairs, and if so can I remove the intervening blank coumns?"

unfortunately, they will not be contiguous and they will not be separated by blank columns. They will be separated by columns that have numbers in them.
BUT I can make it so they are contiguous by using "=" sign. the equal sign will reference each cell of each of the relevant the relevant columns (two of them at a time) and put them in column x and column y next to each other. i can do that.
does that help?

so really all i am saying is that i make it so that I have multiple sets (up to 12 columns) of data. let's say they are in a row
{A,B}, {C,D} {E,F} etc. i dont know how many sets i will have. sometimes ill have 10 sometimes 6 but never more than 12. as long as the macro somehow realizes that the values in each of these columns are also contained in another column and called through "=" sign.

i hope this makes sense.
please feel free to ask me any more questions.

[blumfeld0]

If what I am saying is not possible, then I might have an idea.
i can create up to 12 worksheets to regroup the data.
can I then run your one macro on each worksheet?

this will get tedious as i would have to do this thousands of times.
so if this is possible, is there a way to run your one macro on all 12 worksheets simultaneously?



EDIT: xld your cool macro works well. i tried it for one data set and it works good.

I can simply create 12 worksheets. modify your macro 12 times for each worksheet.
but how do i run all 12 macros at the same time. and tell excel to run macro A for worksheet x, run macro B for worksheet y etc.

thank you!

[Bob Phillips]

Everything is possible, we just need to understand the rules.

Having columns with numbers between the columns of target data makes it hard, but if there is some way to mark the target columns, we can use that. Would it be easy for you to add a row #1 with an id (any id) in JUST the target columns that we could work upon?