Special concatenation

[Ingolf]

Goog Day,

Need some help for some special concatenation.

ConCatSpecial.jpg

The date look like:
AA NAME CC DD EE FF GG HH1
1 BEER 1 0 1 1 1
2 VODCA 1 0 1 1 1
3 COGNAC 1 0 1 1 1
4 WHISKY 1 0 1 1 1

and need a VBA cod to do this:

1;"BEER";"1";"0.00";"1";"1";"1";"";
2;"VODCA";"1";"0.00";"1";"1";"1";"";
3;"COGNAC";"1";"0.00";"1";"1";"1";"";
4;"WHISKY";"1";"0.00";"1";"1";"1";"";

in other sheet.
Thank you.

[Rick Rothstein]

Because this forum's comment processor "eats" multiple spaces, it is hard to tell what columns your original data is in. When I copy/paste it into a worksheet, the column labeled HH1 is empty. Is it possible for there to be blank cells within the data... for example, could cells C2, D2, F2, G2 with E2 and H2 being empty, or will all filled cells always be contiguous across the row?

[Ingolf]

Thanks for the reply guru Rick,

Indeed column H is very often empty. Therefore I need to defend this sign "" (double quote).
It is not possible to Cn, Dn, En, Fn, and Gn to be empty. And if there are empty rows means that no longer of interest. The cells can be only 0 or a positive number ...


Usual I have between 2000 to 7000 rows.

Thank you

[Rick Rothstein]

There was a minor discrepancy in the output between what you posted in Message #1 and what you showed in on Sheet2 of the file you attached for the fourth field... Message #1 shows the fourth field as being formatted as number with two decimal places whereas your file shows you want that field to be a 3-digit whole number with leading zeroes. I assume Message #1 was correct; here is a macro for you to consider (I assumed sheets named Sheet1 and Sheet2 like you showed in the file you attached)...

Sub Booze() Dim R As Long, Data As Variant, Results As Variant Data = Sheets("Sheet1").Range("A2:H" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).row) ReDim Results(1 To UBound(Data), 1 To 1) For R = 1 To UBound(Data) Data(R, 4) = Format(Data(R, 4), "0.00") Results(R, 1) = Join(Application.Index(Data, R, Split("1 2 3 4 5 6 7 8")), """;""") Results(R, 1) = Replace(Results(R, 1), """", "", , 1) & """;" Next Sheets("Sheet2").Range("A1").Resize(UBound(Results)) = Results End Sub

[Ingolf]

Extraordinary, excellent

Yes, message #1 was correct.

Rick you are the best of the best.

Thank you very much.

[snb]

another approach:

Sub M_snb()
  Sheet1.UsedRange.Offset(1).Copy
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .GetFromClipboard
    sn = Filter(Split(Replace(Replace(Replace(.GetText, vbTab, Chr(34) & ";" & Chr(34)), ";" & Chr(34) & vbCrLf, "|" & Chr(34)), vbLf, Chr(34) & "|" & Chr(34)), "|"), ";")
  End With

  Sheet2.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

[snb]

Sub M_snb()
  Sheet1.UsedRange.Offset(1).Copy
  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .GetFromClipboard
    sn = Split(Replace(Replace(.gettext, vbTab, """;"""), vbCr, """" & vbCr), vbCrLf)
    
    For j = 0 To UBound(sn)
      sn(j) = Replace(sn(j), """", "", , 1)
    Next
  End With

  Sheet2.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

[DocAElstein]

CODE......

. I must get around to learning / practicing that "thing" of yours as well sometime - using the clipboard, then manipulating the text string with a "One liner…"
( gives the results , just not the 0.00 - but neither does mine really, and looping like Rick does would take away the nice "one liner " bit.. shame

[snb]

"0.00" can't be returned, since the value =0, only the formatted presentation of that value is 0.00

[DocAElstein]

"0.00" can't be returned, since the value =0, only the formatted presentation of that value is 0.00

. This seems to work, as long as The Elements are Dimensioned as a String.

Sub FormatInArray()
'Worksheet info
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1") 'Sheet Info
 
Dim Data(1 To 2) As String
Let Data(1) = 0
Let Data(2) = 0
Let Data(1) = Format(Data(1), "0.00")
Let ws1.Cells(2, 4).Value = Data(1)
Let ws1.Cells(3, 4).Value = Data(2)
 
End Sub

Using Excel 2007

Row\Col	D
2	0,00
3	0

Sheet1

. But I guess that is what you are saying, sort of - To do with Formatting.. or Wotever (Whatever)

. I just did not realise that one can Put Format into Part of an Array in that way such that the output comes out in the spreadsheet like that. This is wot (what) I learned from Rick's code. That could come in handy as another "work around" I often need to cure problems I get with excel annoyingly changing comers to points, and / or changing numbers to date formats etc...