Copying Cell Values from One Sheet and Paste based on Condition

[madhuchelliah]

Hello Folks, I have dynamic data in A column of sheet 1 and detailed information of those data is available in sheet 2. In sheet 2, C column i have those detailed information. Randomly in C column some cell value is "Total". There will be multiple instance of "Total" in C column. The requirement is i have to copy the data in 1st row of "A" column sheet 1 to the A column of sheet 2 where the 1st instance of "Total" is coming in C column. It has to go on like 2nd row data to 2nd instance, 3rd row data to 3rd instance and so on. see the example below for better understanding. Thank you.

Sheet1

A	B
AAAA
BBBB
CCCC
DDDD
EEEE
FFFF

Sheet2

A	B	C
		XXXX
AAAA		Total
		XXXX
		XXXXX
BBBB		Total
		XXXXX
CCCC		Total
		XXXXX
		XXXX
DDDD		Total

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C
1
2			XXXX
3	AAAA		Total
4			XXXX
5			XXXXX
6	BBBB		Total
7			XXXXX
8	CCCC		Total
9			XXXXX
10			XXXX
11	DDDD		Total
Sheet2

Cell Formulas
Range	Formula
A2:A11	A2	=IF(C2="total",INDEX(Sheet1!$A$2:$A$7,COUNTIFS(C$2:C2,C2)),"")

 

[madhuchelliah]

He

How about

+Fluff New.xlsm
	A	B	C
1
2			XXXX
3	AAAA		Total
4			XXXX
5			XXXXX
6	BBBB		Total
7			XXXXX
8	CCCC		Total
9			XXXXX
10			XXXX
11	DDDD		Total
Sheet2

Cell Formulas
Range	Formula
A2:A11	A2	=IF(C2="total",INDEX(Sheet1!$A$2:$A$7,COUNTIFS(C$2:C2,C2)),"")

Hello Fluff, it is working great. Thanks for your efforts. I forget to mention, that i need the solution in VBA. Sorry for the trouble.

 

[Fluff]

Maybe

Sub madhuchelliah()
   Dim Cl As Range
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   With Sheets("Sheet2")
      .Range("C:C").Replace "Total", "=xxxTotal", xlWhole, , False, , False, False
      For Each Cl In .Range("C:C").SpecialCells(xlFormulas, xlErrors)
         i = i + 1
         Cl.Offset(, -2).Value = Ary(i, 1)
      Next Cl
      .Range("C:C").Replace "=xxxTotal", "Total", xlWhole, , False, , False, False
   End With
End Sub

 

[madhuchelliah]

Maybe

Sub madhuchelliah()
   Dim Cl As Range
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   With Sheets("Sheet2")
      .Range("C:C").Replace "Total", "=xxxTotal", xlWhole, , False, , False, False
      For Each Cl In .Range("C:C").SpecialCells(xlFormulas, xlErrors)
         i = i + 1
         Cl.Offset(, -2).Value = Ary(i, 1)
      Next Cl
      .Range("C:C").Replace "=xxxTotal", "Total", xlWhole, , False, , False, False
   End With
End Sub

Hello Fluff, it is working great. Thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.