Reason Segregation [SOLVED]

[amit_gupta123]

I have a list as under in column A

40-Present with document

0046DDCENPAY

31-Instrument out dated / stale

0046DDCENPAY

0083DDCENPAY

0083DDCENPAY

1400DDCENPAY

1400DDCENPAY

0286DDCENPAY

34-Cheque irregularly drawn / amount in words and figures differ

0826DDCENPAY

39-Image not clear, present again with paper

6294DDCENPAY

5480DDCENPAY

0106DDCENPAY

0168DDCENPAY

And I want the desired result as under

[pre]

0046DDCENPAY	40-Present with document
0046DDCENPAY	31-Instrument out dated / stale
0083DDCENPAY	31-Instrument out dated / stale
0083DDCENPAY	31-Instrument out dated / stale
1400DDCENPAY	31-Instrument out dated / stale
1400DDCENPAY	31-Instrument out dated / stale
0286DDCENPAY	31-Instrument out dated / stale
0826DDCENPAY	34-Cheque irregularly drawn / amount in words and figures differ
6294DDCENPAY	39-Image not clear, present again with paper
5480DDCENPAY	39-Image not clear, present again with paper
0106DDCENPAY	39-Image not clear, present again with paper
0168DDCENPAY	39-Image not clear, present again with paper

[/pre]

 

[Luke M]

This should rearrange things for you. Assumption is that data starts in A2. Output goes into col C and D, starting at row 2.

[pre]

Sub ChangeOutput()
Dim xLabel As String
Dim i As Long, x As Long
Dim lastRow As Long
Dim MyString As String

'Where is the last row
lastRow = Range("A2").End(xlDown).Row

i = 2
'Start in row 2

Application.ScreenUpdating = False
For x = 2 To lastRow
MyString = Cells(x, "A").Value
If Mid(MyString, 3, 1) = "-" Then 'New header
xLabel = MyString
Else
'Output to col C and D
Cells(i, "C") = MyString
Cells(i, "D") = xLabel
i = i + 1
End If
Next x
Application.ScreenUpdating = True
End Sub

[/pre]

 

[amit_gupta123]

thanks a lot....but can it be done using formulas instead of code

 

[amit_gupta123]

Sir, can u please advise the books for excel formulas & VBA. I am trying to gain knowledge but still struggling. Please guide n advice

 

[Haseeb A]

Hello Amit,


If sublist always contains "DDCENPAY" & header always contains "-"; then here is one way with formula.


Assuming data is in A2:A100


In B2, enter with CTRL+SHIFT+ENTER not JUST ENTER


=INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("DDCENPAY",A$2:A$100)),ROW(A$2:A$100)),ROWS(B$2:B2)))


In C2, with CTRL+SHIFT+ENTER


=LOOKUP(100^100,FIND("-",A$2:INDEX(A:A,SMALL(IF(A$2:A$100=B2,ROW(A$2:A$100)),COUNTIF(B$2:B2,B2)))),A$2:A$100)


Then copy B2:C2 & copy down.

 

[amit_gupta123]

wow...wonderful...you are champ guys ...thks Luke and hasseb