Matching List of Name is different Sheets - Copying Adjacent cell values from one to the other

[Tron027]

Hi,

This is my first time posting here, and I understand that this is a widely asked question, so please bear with me. Unfortunately, I can't seem to get it right for this situation.

I have two worksheets (WS1 and WS2).

WS1 has a list of company names in column B. Each of the 15 company names is repeated under 5 different headings (Opening Balance, Receipts, Payments, Closing Balance & Special Items). Next to each company name is a row of values stretching to column Q representing the amounts per company for their Opening Balance, Receipts, etc.

This report (Report 1) has a specific date allocated to it. The same report is repeated above or below with the same report layout for different periods, linked to a particular "start date".

On WS2, we have Report 2 containing a matching start date, but here each section is split by the company name, and each heading from Report 1 is listed as a line item under the company name.

The first requirement is for Report 2 on WS2 to be matched with Report 1 using the start date linked to each.

After this has been done, the data from Report 1 should be copied per company under each heading and pasted to the matching company name and related line items on WS2.

I have added extracts from the workbook below.

Thanks

WS1

Report 1

01/01/2021

Opening Balance

Google

Apple

Microsoft

IBM

BP

Shell

Naspers

Walmart

Amazon

Toyota

Ford

Volkswagen

Tesla

Tencent

Berkshire Hathaway

Receipts

Google

Apple

Microsoft

IBM

BP

Shell

Naspers

Walmart

Amazon

Toyota

Ford

Volkswagen

Tesla

Tencent

Berkshire Hathaway

Payments

Google

Apple

Microsoft

IBM

BP

Shell

Naspers

Walmart

Amazon

Toyota

Ford

Volkswagen

Tesla

Tencent

Berkshire Hathaway

Closing Balance

Google

Apple

Microsoft

IBM

BP

Shell

Naspers

Walmart

Amazon

Toyota

Ford

Volkswagen

Tesla

Tencent

Berkshire Hathaway

ABC Foods

Special Items

Google

Apple

Microsoft

IBM

BP

Shell

Naspers

Walmart

Amazon

Toyota

Ford

Volkswagen

Tesla

Tencent

Berkshire Hathaway

WS2

Report 2

01/01/2021

Google

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Apple

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Microsoft

Opening Balance

Receipts

Payments

Closing Balance

Special Items

IBM

Opening Balance

Receipts

Payments

Closing Balance

Special Items

BP

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Shell

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Naspers

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Walmart

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Amazon

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Toyota

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Ford

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Volkswagen

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Tesla

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Tencent

Opening Balance

Receipts

Payments

Closing Balance

Special Items

Berkshire Hathaway

Opening Balance

Receipts

Payments

Closing Balance

Special Items

 

[DanteAmor]

Hi and welcome to MrExcel

The same report is repeated above or below with the same report layout for different periods, linked to a particular "start date".

The process must be repeated several times?

The first requirement is for Report 2 on WS2 to be matched with Report 1 using the start date linked to each.

According to your example you have to look for the date "01/01/2021" in sheet ws2, but in which row?

After this has been done, the data from Report 1 should be copied per company under each heading and pasted to the matching company name and related line items on WS2.

I am trying to understand what information to copy and where to paste.

In the forum there is a tool (see my signature), which you can download and install in your excel; the XL2BB tool will allow you to paste data directly from your sheet here. That way you can show an example like this:

WS1

Libro1
	A	B	C	D	E	F	G	H	I	J
1		Report 1
2		01/01/2021					02/01/2021
3		Opening Balance			EXAMPLES		Opening Balance			EXAMPLES
4		Google	1	a			Google	1	a
5		Apple	2	b			Apple	2	b
6		Microsoft	3	c			Microsoft	3	c
7
8		Receipts					Receipts
9		Google	6	f			Google	6	f
10		Apple	7	g			Apple	7	g
11		Microsoft	8	h			Microsoft	8	h
12
13		Payments					Payments
14		Google	11	k			Google	11	k
15		Apple	12	l			Apple	12	l
16		Microsoft	13	m			Microsoft	13	m
ws1

WS2

Libro1
	A	B	C	D	E	F	G	H	I	J
1	Report 2
2	01/01/2021			02/01/2021			03/01/2021			04/01/2021
3	Google
4	Opening Balance	1	a
5	Receipts	6	f
6	Payments	11	k
7
8	Apple
9	Opening Balance	2	b
10	Receipts	7	g
11	Payments	12	l
12
13	Microsoft
14	Opening Balance	3	c
15	Receipts	8	h
16	Payments	13	m
ws2

 

[Tron027]

Hi Dante,

Thank you for the quick reply in this regard.

The process must be repeated several times?

Yes, every week a new WS1 Report is compiled, this report shows a consolidated view of forecast data as submitted by various companies for that week. I need the excel workbook to take the information from WS1 on a weekly basis and reshuffle/restructure it to a new layout in the Report on WS2 based on the criteria of the two dates matching up.

According to your example you have to look for the date "01/01/2021" in sheet ws2, but in which row?

That is correct, each week a new Report structure will be created in WS2 with a date, "01/02/2021" in this example. This date will always be found in Column B, in the cell above that weeks report heading - i.e. B4

The corresponding date can be found on WS1 also located in column B.

I have had to adjust my example to only have 5 companies to fit into this posting.

The movement of information would be as follows - Using the first company "Google" as an example.

WS1, C8:Q8 - Copy (Opening Balance: Google)
WS2, C14:Q14 - Paste (Google: Opening Balance)

WS1, C15:Q15 - Copy (Receipts: Google)
WS2, C15:Q15 - Paste (Google: Receipts)

WS1, C22:Q22 - Copy (Payments: Google)
WS2, C16:Q16 - Paste (Google: Payments)

WS2, C17:Q17 - SUM(C14:C16) (Google: Closing Balance)

WS1, C36:Q36 - Copy (Special Item: Google)
WS2, C18:Q18 - Paste (Google: Special Item)

etc.

At the end of the day, the rationality check would be to ensure that the "Consolidated / Group" section at the top of the Report found on WS2 (C7:Q12), which is a SUM formula of all company sections below, matches back to the colour highlighted rows (C7:Q7, C14:Q14, C21:Q21, C28:Q28, C35:Q35) on the Report found in WS1.

Example.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	WS1
3
4	01-Feb-21
5	Report	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15
6
7	Opening Balance	3,289	(5,521)	9,576	(553)	(6,909)	(21,134)	(582)	15,277	(26,310)	1,012	(8,767)	1,601	11,054	(21,288)	14,970
8	Google	(2,364)	2,606	4,604	8,476	6,486	(6,500)	7,032	2,923	(9,651)	(9,050)	(21)	7,492	2,856	(6,316)	(1,357)
9	Apple	7,808	(7,774)	79	9,259	(1,725)	(3,765)	(2,175)	7,633	(5,039)	(7,423)	5,511	2,936	(6,932)	5,402	6,291
10	Microsoft	3,239	9,985	2,888	(909)	(7,358)	(3,338)	4,883	7,764	(1,832)	8,799	(4,923)	(2,769)	9,727	(6,776)	7,414
11	IBM	(8,565)	(6,184)	8,055	(9,955)	3,850	(1,240)	(9,062)	4,841	(5,003)	1,474	(9,799)	(8,423)	2,815	(6,995)	(456)
12	BP	3,171	(4,154)	(6,050)	(7,424)	(8,162)	(6,291)	(1,260)	(7,884)	(4,785)	7,212	465	2,365	2,588	(6,603)	3,078
13
14	Receipts	17,306	11,461	11,778	11,886	8,957	11,099	9,859	12,023	8,213	14,486	11,790	7,370	9,229	14,332	16,195
15	Google	3,747	3,142	4,264	1,356	1,780	4,641	3,020	2,993	436	3,787	3,415	1,603	620	3,744	3,149
16	Apple	4,040	685	2,106	2,787	1,895	3,119	3,194	3,173	552	2,319	2,694	1,032	2,130	3,110	2,324
17	Microsoft	3,122	248	1,691	3,000	988	2,616	1,484	1,011	1,747	4,030	1,822	2,139	2,863	3	4,937
18	IBM	4,360	4,307	2,580	4,274	209	32	766	3,299	643	1,062	671	2,032	2,298	2,808	4,065
19	BP	2,037	3,079	1,137	469	4,085	691	1,395	1,547	4,835	3,288	3,188	564	1,318	4,667	1,720
20
21	Payments	(9,825)	(6,799)	(7,048)	(4,778)	(6,744)	(5,953)	(7,037)	(5,679)	(9,854)	(4,397)	(6,615)	(8,348)	(7,395)	(6,414)	(4,183)
22	Google	(4,033)	(2,160)	(910)	(1,669)	(709)	(2,015)	(721)	(750)	(2,407)	(1,282)	(1,949)	(682)	(2,023)	(1,313)	(648)
23	Apple	(1,210)	(748)	(1,959)	(1,430)	(1,203)	(2,060)	(577)	(3)	(738)	(210)	(1,078)	(2,266)	(1,379)	(238)	(1,779)
24	Microsoft	(2,010)	(1,170)	(2,067)	(106)	(656)	(679)	(1,540)	(2,226)	(2,427)	(13)	(1,840)	(2,389)	(1,394)	(991)	(936)
25	IBM	(2,310)	(565)	(850)	(1,226)	(1,872)	(701)	(2,460)	(2,177)	(1,859)	(900)	(528)	(2,345)	(2,489)	(1,560)	(520)
26	BP	(262)	(2,156)	(1,262)	(347)	(2,304)	(498)	(1,739)	(523)	(2,423)	(1,992)	(1,220)	(666)	(110)	(2,312)	(300)
27
28	Closing Balance	10,770	(859)	14,306	6,555	(4,696)	(15,988)	2,240	21,621	(27,951)	11,101	(3,592)	623	12,888	(13,370)	26,982
29	Google	(2,650)	3,588	7,958	8,163	7,557	(3,874)	9,331	5,166	(11,622)	(6,545)	1,445	8,413	1,453	(3,885)	1,144
30	Apple	10,638	(7,837)	226	10,616	(1,033)	(2,706)	442	10,803	(5,225)	(5,314)	7,127	1,702	(6,181)	8,274	6,836
31	Microsoft	4,351	9,063	2,512	1,985	(7,026)	(1,401)	4,827	6,549	(2,512)	12,816	(4,941)	(3,019)	11,196	(7,764)	11,415
32	IBM	(6,515)	(2,442)	9,785	(6,907)	2,187	(1,909)	(10,756)	5,963	(6,219)	1,636	(9,656)	(8,736)	2,624	(5,747)	3,089
33	BP	4,946	(3,231)	(6,175)	(7,302)	(6,381)	(6,098)	(1,604)	(6,860)	(2,373)	8,508	2,433	2,263	3,796	(4,248)	4,498
34
35	Special Items	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000
36	Google	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000
37	Apple	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
38	Microsoft	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000
39	IBM	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000
40	BP	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000
CA Forecast

Cell Formulas
Range	Formula
B4	B4	=B43+7
C35:Q35,C21:Q21,C14:Q14,C7:Q7	C7	=SUM(C8:C12)
C28:Q33	C28	=SUM(C7,C14,C21)

Example.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	WS2
3
4	01-Feb-21
5	Report	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15
6
7	Consolidated
8	Opening Balance	3,289	(5,521)	9,576	(553)	(6,909)	(21,134)	(582)	15,277	(26,310)	1,012	(8,767)	1,601	11,054	(21,288)	14,970
9	Receipts	17,306	11,461	11,778	11,886	8,957	11,099	9,859	12,023	8,213	14,486	11,790	7,370	9,229	14,332	16,195
10	Payments	(9,825)	(6,799)	(7,048)	(4,778)	(6,744)	(5,953)	(7,037)	(5,679)	(9,854)	(4,397)	(6,615)	(8,348)	(7,395)	(6,414)	(4,183)
11	Closing Balance	10,770	(859)	14,306	6,555	(4,696)	(15,988)	2,240	21,621	(27,951)	11,101	(3,592)	623	12,888	(13,370)	26,982
12	Special Items	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000	130,000
13	Google
14	Opening Balance	(2,364)	2,606	4,604	8,476	6,486	(6,500)	7,032	2,923	(9,651)	(9,050)	(21)	7,492	2,856	(6,316)	(1,357)
15	Receipts	3,747	3,142	4,264	1,356	1,780	4,641	3,020	2,993	436	3,787	3,415	1,603	620	3,744	3,149
16	Payments	(4,033)	(2,160)	(910)	(1,669)	(709)	(2,015)	(721)	(750)	(2,407)	(1,282)	(1,949)	(682)	(2,023)	(1,313)	(648)
17	Closing Balance	(2,650)	3,588	7,958	8,163	7,557	(3,874)	9,331	5,166	(11,622)	(6,545)	1,445	8,413	1,453	(3,885)	1,144
18	Special Items	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000	50,000
19	Apple
20	Opening Balance	7,808	(7,774)	79	9,259	(1,725)	(3,765)	(2,175)	7,633	(5,039)	(7,423)	5,511	2,936	(6,932)	5,402	6,291
21	Receipts	4,040	685	2,106	2,787	1,895	3,119	3,194	3,173	552	2,319	2,694	1,032	2,130	3,110	2,324
22	Payments	(1,210)	(748)	(1,959)	(1,430)	(1,203)	(2,060)	(577)	(3)	(738)	(210)	(1,078)	(2,266)	(1,379)	(238)	(1,779)
23	Closing Balance	10,638	(7,837)	226	10,616	(1,033)	(2,706)	442	10,803	(5,225)	(5,314)	7,127	1,702	(6,181)	8,274	6,836
24	Special Items	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000	25,000
25	Microsoft
26	Opening Balance	3,239	9,985	2,888	(909)	(7,358)	(3,338)	4,883	7,764	(1,832)	8,799	(4,923)	(2,769)	9,727	(6,776)	7,414
27	Receipts	3,122	248	1,691	3,000	988	2,616	1,484	1,011	1,747	4,030	1,822	2,139	2,863	3	4,937
28	Payments	(2,010)	(1,170)	(2,067)	(106)	(656)	(679)	(1,540)	(2,226)	(2,427)	(13)	(1,840)	(2,389)	(1,394)	(991)	(936)
29	Closing Balance	4,351	9,063	2,512	1,985	(7,026)	(1,401)	4,827	6,549	(2,512)	12,816	(4,941)	(3,019)	11,196	(7,764)	11,415
30	Special Items	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000	10,000
31	IBM
32	Opening Balance	(8,565)	(6,184)	8,055	(9,955)	3,850	(1,240)	(9,062)	4,841	(5,003)	1,474	(9,799)	(8,423)	2,815	(6,995)	(456)
33	Receipts	4,360	4,307	2,580	4,274	209	32	766	3,299	643	1,062	671	2,032	2,298	2,808	4,065
34	Payments	(2,310)	(565)	(850)	(1,226)	(1,872)	(701)	(2,460)	(2,177)	(1,859)	(900)	(528)	(2,345)	(2,489)	(1,560)	(520)
35	Closing Balance	(6,515)	(2,442)	9,785	(6,907)	2,187	(1,909)	(10,756)	5,963	(6,219)	1,636	(9,656)	(8,736)	2,624	(5,747)	3,089
36	Special Items	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000	30,000
37	BP
38	Opening Balance	3,171	(4,154)	(6,050)	(7,424)	(8,162)	(6,291)	(1,260)	(7,884)	(4,785)	7,212	465	2,365	2,588	(6,603)	3,078
39	Receipts	2,037	3,079	1,137	469	4,085	691	1,395	1,547	4,835	3,288	3,188	564	1,318	4,667	1,720
40	Payments	(262)	(2,156)	(1,262)	(347)	(2,304)	(498)	(1,739)	(523)	(2,423)	(1,992)	(1,220)	(666)	(110)	(2,312)	(300)
41	Closing Balance	4,946	(3,231)	(6,175)	(7,302)	(6,381)	(6,098)	(1,604)	(6,860)	(2,373)	8,508	2,433	2,263	3,796	(4,248)	4,498
42	Special Items	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000
LS Forecast

Cell Formulas
Range	Formula
B4	B4	='CA Forecast'!B4
C8:Q12	C8	=SUM(C14,C20,C26,C32,C38)
C41:Q41,C35:Q35,C29:Q29,C23:Q23,C17:Q17	C17	=SUM(C14:C16)

 

[DanteAmor]

I only have the doubt.
In which row of the sheet "CA Forecast" you have the data "Report", in your example, it appears in the row 5. If it is not row 5, then change the 5 to the row number in the next line of the macro:

lc = sh1.Cells(5, Columns.Count).End(1).Column

Try this:

Sub consolidate_report()
  Dim sh1 As Worksheet
  Dim a As Variant, b As Variant, c As Variant
  Dim hd As Variant, hds As Variant
  Dim i As Long, j As Long, k As Long, lr As Long, lc As Long, m As Long, n As Long
  Dim f As Range
  Dim dic As Object
  Dim itm As String
  
  Set dic = CreateObject("Scripting.Dictionary")
  Set sh1 = Sheets("CA Forecast")
  lr = sh1.Range("B" & Rows.Count).End(3).Row
  lc = sh1.Cells(5, Columns.Count).End(1).Column
  
  hds = Array("Opening Balance", "Receipts", "Payments", "Closing Balance", "Special Items")
  ReDim c(0 To UBound(hds) + 1)
  a = sh1.Range("B1", sh1.Cells(lr, lc)).Value
  ReDim b(1 To UBound(a, 1) * 2, 1 To UBound(a, 2))
  
  i = 1
  For Each hd In hds
    Set f = sh1.Range("B:B").Find(hd, , xlValues, xlPart, , , False)
    If Not f Is Nothing Then
      c(n) = f.Row
      n = n + 1
      b(i, 1) = hd
      For j = 2 To UBound(a, 2)
        b(i, j) = a(f.Row, j)
      Next
      i = i + 1
    End If
  Next
  c(UBound(c)) = lr + 1
  
  For k = 0 To UBound(c) - 1
    For m = c(k) + 1 To c(k + 1) - 1
      itm = Trim(a(m, 1))
      If itm <> "" Then
        If Not dic.exists(itm) Then
          b(i, 1) = itm
          i = i + 1
          dic(itm) = i
          n = dic(itm)
          i = i + UBound(hds) + 1
        Else
          n = dic(itm) + 1
          dic(itm) = n
        End If
        b(n, 1) = hds(k)
        For j = 2 To UBound(a, 2)
          b(n, j) = a(m, j)
        Next
      End If
    Next
  Next
  
  With Sheets("LS Forecast")
    .Range("B8", .Cells(Rows.Count, Columns.Count)).ClearContents
    .Range("B8").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End With
End Sub