excel formula to equal data from another sheet based on cell value

[demonicsoul]

Hi,

From the screenshots below, if sheet1.column A has a value of 1, then in Sheet2, make cells A1 and A2 equal the value in Sheet1.Cell(B1) and so on going down the list. So basically, whenever there is a 1, the value from column B should be in two rows on Sheet2 and if there is a 2, then value in column B is only in 1 row in Sheet2. Is there a formula or VBA that will satisfy this logic? Thanks

 

[Bosquedeguate]

Something like this code should do it.

I had the source and destination on the same worksheet
Source Data in Columns A, and B
Output values in Column E (the range address will need to be changed to output the results a different sheet
like Range("Sheet2!A" & n) = ...
But other than this change the code should work for you.

There is no code to clear the column of previous values in this version.

Option Explicit
Sub ExpandMenu()
  Dim lr As Long
  Dim r, i, n As Long
  lr = Range("A" & Rows.Count).End(xlUp).Row
  n = 1
  For r = 1 To lr
    For i = 1 To IIf(Range("A" & r) = 1, 2, 1) 'if you want more than 2 entries of the value change the number here (e.g. the 2 to a 3 for example)
      Range("E" & n) = Range("B" & r)
      n = n + 1
    Next i
  Next r
End Sub

Book1
	A	B	C	D	E
1	1	chicken			chicken
2	2	beef			chicken
3	1	chicken			beef
4	3	fish			chicken
5					chicken
6					fish
Sheet1

 

[demonicsoul]

thanks!