How to Categorize mixed data from a report like worksheet

RafiSharif

New Member
Joined
Apr 3, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
I have a worksheet that has data stored in a report like format.
Sales data with order number & other information but the issue is they are separated by country & the country name is on top of a table like structure & it repeat the same structure only the country name changes every time.
Is there anyway to have all the records in the same structure and removing the total from each country, & add the country names in a new column corresponding to the data.
 

Attachments

  • Screenshot_20210404-085433~2.jpg
    Screenshot_20210404-085433~2.jpg
    127.3 KB · Views: 4

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
Hi RafiSharif,

Could you post the first 20 or so rows of the report as text?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
Is that really the start of the report, because there's no Netherlands start row?
 

RafiSharif

New Member
Joined
Apr 3, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Is that really the start of the report, because there's no Netherlands start row?
No that isn't the start of the spreadsheet i only have this part but the start is similar to the New Zealand section.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
This requires that the first row of the report is a country name, like this
RafiSharif.xlsx
ABCDEFGHIJK
1NETHERLANDS
2Order DateSO NumberCustomer #Customer NameShipping ChargeAmountTax NameTax RateTax AmountTotal Amount
312/16/201510000592303084LabID Technologies B.V.$ 0.00$ 1,425.00VAT21 %$ 299.25$ 1,724.25
412/21/201510000598303452Limgroup B.V.$ 15.00$ 210.40VAT21 %$ 47.33$ 272.73
512/23/201510000604803084LabID Technologies B.V.$ 0.00$ 80.70VAT21 %$ 16.92$ 97.62
611/10/2015R10000501303604Kwekerij Piet Vijverberg B.V.$ 0.00$ 0.00VAT21 %$ 0.00$ 0.00
712/17/2015R10000593702096Radboud umc$ 0.00$ 0.00None0 %$ 0.00$ 0.00
8Total for NETHERLANDS:$ 422.00$ 14,619.35$ 3,158.66$ 18,200.01
9NEW ZEALAND
1007/09/201510000204401400Pathology Associates$ 67.00$ 950.17None0 %$ 0.00$ 1,017.17
1107/21/201510000229902273Slipstream Automation$ 65.94$ 238.00None0 %$ 0.00$ 303.94
1207/31/201510000258601174Canterbury District Health Board$ 61.00$ 720.00None0 %$ 0.00$ 781.00
Dummy Data (2)


Column N becomes a work column
Cell Formulas
RangeFormula
N2:N14N2=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$9999)/(($B$1:$B$9999<>"")*($B$1:$B$9999<>"SO Number")),ROW()-ROW($P$1)),"")
O2O2=A1
P2:Y14P2=IF($N2="","",INDEX(A:A,$N2))
O3:O14O3=IF(N3="","",IF(AND(INDEX($B$1:$B$9999,N3-1)="",LEFT(INDEX($B$1:$B$9999,N3-1),9)<>"Total for"),INDEX($A$1:$A$9999,N3-1),O2))
 
Solution

Forum statistics

Threads
1,141,051
Messages
5,703,967
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top